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

Question

I'm 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 I'm using is:

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

SQLMenace · Accepted Answer · 2010-10-22 17:41:54Z

5

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

answered Oct 22, 2010 at 17:41

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Brad Over a year ago

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);

SQLMenace Over a year ago

@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

Alejandro Over a year ago

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 Over a year ago

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?

SQLMenace Over a year ago

ALTER DATABASE aguasdelbosque SET MULTI_USER

Garett · Accepted Answer · 2010-10-22 19:21:22Z

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