0

I am developing a windows form application in c# visual studio 2010, where I want to create a feature in which a user is allowed to restore and backup the database by itself. The problem is that I am using a standalone mdf file.

i am using sql server express by attaching mdf file to the application, when i try to backup using query it's work but when i try restore the database using query it say that it's say that doesn't have permission to alter table. And then i try to using smo but it say that unable to open the file. So i am wondering if there is any option

luhuiya
  • 2,129
  • 21
  • 20
  • Hope this article helps you http://www.codeproject.com/Articles/26390/SQL-Server-2005-Database-Backup-and-Restore-using – udaya726 May 28 '12 at 09:08
  • You need to describe you problem further and also write a bit about what you have tried so far. – Fredrik Ljung May 28 '12 at 09:09
  • r u using mysql database......? – Glory Raj May 28 '12 at 09:10
  • mdf hints he is using SQL Server, and by standalone I would suspect he means SQL Server Express by attaching the mdf/ldf files. Just guessing, though – Sascha May 28 '12 at 09:15
  • Did you want to allow the user to Backup/Restore database from Sql Server Management Console (from inside Sql)? Or just want to execute database backup command on Button Click or something similar? – vpv May 28 '12 at 10:10
  • @V.P.Verma, yes i want to restore by button click on the form – luhuiya May 28 '12 at 10:37
  • @udaya726, i have tried it but the database in the server is all temp db which is not allowed to be backup and restore – luhuiya May 28 '12 at 14:29

1 Answers1

0

To create a backup using t-sql you can use:

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\BACKUP\AdventureWorks.bak'
GO

To restore use:

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\BACKUP\AdventureWorks.bak'
GO

Add WITH REPLACE to replace the existing database.

You can fire those SQL commands using ExecuteNonQuery.

Note: this works on Standard SQL Server, but I have not used it on SQL Server Express with an attached database file.

Sascha
  • 10,231
  • 4
  • 41
  • 65
  • i have try it but it is error "you do not have permission to alter database or the database is not exsists" – luhuiya May 28 '12 at 10:55
  • User does not have permission to alter database 'DatabaseA', the database does not exist, or the database is not in a state that allows access checks. ALTER DATABASE statement failed. - - – luhuiya May 28 '12 at 10:56
  • it appear when i try to restore – luhuiya May 28 '12 at 10:56
  • If there are open connections to your database, you need to close them first. AKTER DATABASE will fail after a certain amount of time when there are open connections. – Sascha May 28 '12 at 10:59
  • so do you mean i need to using another application to restore and backup it since its being used? – luhuiya May 28 '12 at 14:28