Ever get this error message?
Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally.
You can’t do a restore will connections to that database still exist!
If you have jobs that do restores of databases for one reason or another, I bet you have seen this message. I have a job to do a nightly restore of a backup, but some users just never disconnect even though I continually shake my finger. So what do I do? I kill all the processes from problem users before starting the restore.
--Create the cursor
declare mycursor cursor
for
-- Query for processes by login and database
select spid, Loginame
from master..sysProcesses
where Loginame='someuser' and dbid=db_id('somedb')
open mycursor
declare @spid int, @loginame varchar(255), @cmd varchar(255)
-- Loop through the cursor, killing each process
Fetch NEXT FROM MYCursor INTO @spid, @loginame
While (@@FETCH_STATUS <> -1)
begin
-- I don't really know why this is necasary, but it is.
select @cmd = 'kill ' + cast(@spid as varchar(5))
exec(@cmd)
Fetch NEXT FROM MYCursor INTO @spid, @loginame
end
close mycursor
deallocate mycursor
go
I am no TSQL expert, so kudos to this article for helping me figure out why kill @spid wasn’t working.