Before an existing database can be restored, there should be connections using the database in question. If the database is currently in use the RESTORE command fails with below error:
Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
SELECT session_id FROM sys.dm_exec_sessions WHERE DB_NAME(database_id) = 'SqlAndMe'
You need to terminate each of the sessions returned individually by using KILL command.
If there are large number of sessions to kill, or you need to do this on a routine basis it gets boring to do it this way. You can *automate* this using below script, which takes database name as input, and kills all sessions connecting to it.
-- Kill all sessions using a database -- Vishal - http://SqlAndMe.com USE [master] GO DECLARE @dbName SYSNAME DECLARE @sqlCmd VARCHAR(MAX) SET @sqlCmd = '' SET @dbName = 'SqlAndMe' -- Change database name here SELECT @sqlCmd = @sqlCmd + 'KILL ' + CAST(session_id AS VARCHAR) + CHAR(13) FROM sys.dm_exec_sessions WHERE DB_NAME(database_id) = @dbName PRINT @sqlCmd --Uncomment below line to kill --EXEC (@sqlCmd)
Hope This Helps!
If you like this post, do like my Facebook Page –> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe