Every once in a while when I’m trying to restore a backup for a database on Microsoft SQL Server Management Studio (SSMS), I get something like the following error:
Title: Microsoft SQL Server Management Studio
Restore failed for Server ‘[server name]’. (Microsoft.SqlServer.SmoExtended)
Additional information:
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)
From what I understand, this means that some process is holding on to a connection to the database and, therefore, I can’t do anything drastic. Because my job deals with the web, I usually find that the culprit holding on to the database connection is IIS. Typically, if I stop the application pool of the site that’s using the database, or the site itself, I’m able to complete my restore. But sometimes I can’t track it down or I’m just too lazy to do anything about it. So I wrote a little script that kills the connections to a database by putting the database in single user mode and then back into multi-user mode. Here it is for reference:
DECLARE @DB VARCHAR(255)
SET @DB = 'databaseName'
DECLARE @SINGLE_USER VARCHAR(MAX)
DECLARE @MULTI_USER VARCHAR(MAX)
SET @SINGLE_USER = 'ALTER DATABASE {DB} SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
SET @MULTI_USER = 'ALTER DATABASE {DB} SET MULTI_USER WITH ROLLBACK IMMEDIATE'
DECLARE @SCRIPT VARCHAR(MAX)
USE MASTER
SET @SCRIPT = REPLACE(@SINGLE_USER, '{DB}', @DB)
EXECUTE(@SCRIPT)
SET @SCRIPT = REPLACE(@MULTI_USER, '{DB}', @DB)
EXECUTE(@SCRIPT)
I found this concept on an MSDN forum post and I borrowed the idea of using a variable for the the database name from stackoverflow.
Blunderings 