Actually im developing a vb .net desktop app that needs to restore a database using a .bak How to do that?

views:

answers:

+4 Q:

Actually im developing a vb .net desktop app that needs to restore a database using a .bak How to do that?

Im getting this:

"Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally."

First the message told me i must connect to master db to execute the restore but i change it and i got the message above.

The parametrized sql statement im using is :

cmd.CommandText = "RESTORE DATABASE aguasdelbosque " + "FROM DISK = @archivo"

I will appreciated some of your help...

+5 A:

You first need to kick all users out of the database, take a look at Kill All Active Connections To A Database on how to do it

SQLMenace 2010-10-22 17:41:54

or... `USE master GO; DECLARE @SQL VARCHAR(MAX); SELECT @SQL = COALESCE(@SQL, '') + 'Kill ' + CONVERT(VARCHAR, SPId) + ';' FROM MASTER..SysProcesses WHERE SPId <> @@SPId AND [dbid] = DB_ID('DATABASE_NAME'); EXEC(@SQL);`

Brad 2010-10-22 17:44:06

@Brad if you have a lot of connections while that code is running it is possible that other people get in, it is better to put it in single user mode

SQLMenace 2010-10-22 17:46:12

Hi guys thanks a lot... So Can i use this one? : ALTER DATABASE aguasdelbosque SET SINGLE_USER WITH ROLLBACK IMMEDIATE To put in single user mode the database..

Alejandro 2010-10-22 17:52:57

yesy, just remember to make it multi user after the restore

SQLMenace 2010-10-22 17:56:49

Awsome... That helps me a lot man... I get it... Can u help me how to get the database again as multiuser? Or isnt necesary because it is a standalone application running on one single machine with the server running in the same machine?

Alejandro 2010-10-22 18:07:04

ALTER DATABASE aguasdelbosque SET MULTI_USER

SQLMenace 2010-10-22 18:25:30

+1 A:

Another approach to restoring the database is using the SQL Server Management Objects library. To kill all processes, the Server class has the method KillAllProcesses or KillDatabase. The Database class has the methods SetOffline and SetOnline. To restore the database you would use the SqlRestore method of the Restore class. For more information or further reading try the links below.

http://www.sqldbatips.com/showarticle.asp?ID=40

http://social.msdn.microsoft.com/Forums/en/sqlsmoanddmo/thread/08416c9d-0e8d-4021-b5ea-b9dc634c03e8

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/52638e68-5c88-49f1-9b76-6bfa2387da18

http://sqlblogcasts.com/blogs/seanprice/archive/2007/07/11/Killing-ProcessIDs-using-SMO.aspx

http://www.codeproject.com/KB/database/BackupRestoreWithSmo.aspx

Garett 2010-10-22 19:21:22

great man than u

Alejandro 2010-10-22 19:49:05