Obtain Exclusive Access to Restore a SQL Server Database

Database Administrators have always come across scenarios where they need to restore a copy of the latest production database to a test or development environment. This can be either a scheduled process or an adhoc request from the test or development team. The most important things that a DBA needs to do when restoring a database is to ensure that they obtain exclusive access to the SQL Server Database, otherwise the restore process is going to fail. In this article we will look at the different options that are available for a Database Administrator to obtain exclusive access to a SQL Server 2005 or SQL Server 2008 database.

Restore a Database Using SQL Server Management Studio
When trying to restore a SQL Server Database using the SQL Server Management Studio, the process often runs for some time and then it finally fails with the following error message “Exclusive access could not be obtained because the database is in use”. This happens when there are open transactions with the database or many users are connected to the database. SQL Server informs you that it is terminating the restoration process as there are users connected to database you are trying to restore. In order to restore a database in SQL Server you need to be the only user connected or the database should be in SINGLE_USER Mode otherwise the restore process will fail.


 
There are different methods available to disconnect the users that are connected to the SQL Server database:

1. Disconnect users using the sp_KillDatabaseUsers Stored Procedure
2. Detach the Database Using SQL Server Management Studio to Kill Connected Users and Reattach the database and then perform the restoration process
3. Alter the Database Using an ALTER DATABASE Statement such as WITH ROLLBACK IMMEDIATE, WITH ROLLBACK AFTER n SECONDS or WITH NO_WAIT

Now let us see each of the above mentioned methods in detail to know each method in detail.

Disconnecting Connected Database Users
The sp_KillDatabaseUsers stored procedure can be used to disconnect all the connected database users. This stored procedure needs to be compiled in the master database. This Stored Procedure can be used to KILL connected users in SQL Server 2005 and SQL Server 2008. To disconnect all the connected users of AdventureWorks database a DBA needs to execute the following TSQL code:

USE master
Go
EXEC sp_KillDatabaseUsers 'AdventureWorks'
Go

RESTORE DATABASE AdventureWorks
FROM DISK =N'D:\DatabaseBackups\AdventureWorks.BAK'
Go

A DBA needs to make sure that they are not connected to the database before executing the stored procedure. Otherwise, you will receive the error below:

Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.

Stored Procedure Code

USE master
Go
CREATE PROCEDURE sp_KillDatabaseUsers @DBName varchar(100)
AS     
/*

Description: This stored procedure can be used to kill all the connected users in the SQL Server database. This SP code is compatible to SQL Server 2005 / 2008
Author:                 Ashish Kumar Mehta for www.SQL-Server-Performance.com

*/
SET NOCOUNT ON     
     
DECLARE @strSQL varchar(255)     
PRINT '--------------------------'     
PRINT 'Killing Database Users'     
PRINT '--------------------------'     
     
CREATE TABLE #DatabaseUsers   
(     
UserSession int,   
DatabaseID int,   
DatabaseName varchar(100)   
)     

INSERT INTO #DatabaseUsers    
SELECT DISTINCT (request_session_id) AS UserSession ,    
       resource_database_id AS DatabaseID,   
       db_name(resource_database_id) AS DatabaseName    
FROM master.sys.dm_tran_locks    
WHERE resource_type = 'DATABASE'    
AND resource_database_id = db_id(@DBName)   
     
DECLARE DBUserCursor CURSOR READ_ONLY     
FOR SELECT UserSession,DatabaseID FROM #DatabaseUsers WHERE DatabaseName = @DBName     
     
DECLARE @UserSession varchar(10)     
DECLARE @DatabaseID varchar(100)     
   
OPEN DBUserCursor     
     
FETCH NEXT FROM DBUserCursor INTO @UserSession, @DatabaseID     
WHILE (@@fetch_status <> -1)     
BEGIN     
 IF (@@fetch_status <> -2)     
 BEGIN     
 PRINT 'Killed SPID ' + @UserSession     
 SET @strSQL = 'KILL ' + @UserSession     
 EXEC (@strSQL)     
 END     
 FETCH NEXT FROM DBUserCursor INTO  @UserSession, @DatabaseID     
END     
     
CLOSE DBUserCursor     
DEALLOCATE DBUserCursor     
   
DROP table #DatabaseUsers