While trying to restore a database (with replace option), I am getting the below error -
Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Though, before kicking off the refresh, I took the database in 'single user' mode and also used the below script to KILL all the connected sessions.
DECLARE @SPIDSTR varchar(8000)
DECLARE @ConnKilled smallint
DECLARE @DBName varchar(100)
SET @SPIDSTR = ''
SET @ConnKilled = 0;
SET @DBName = '<database>'
SELECT @SPIDSTR=coalesce(@spidstr,',' )+'KILL '+convert(varchar, spid)+ '; '
FROM MASTER..SYSPROCESSES WHERE dbid=db_id(@DBName);
IF LEN(@spidstr) > 0
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
Can you please suggest, what went wrong, in my adhered process?
Atlast, I had to drop the database and now when I am trying to restore, it is working fine.
Kindly suggest with your opinion.