Kill All Connections To A Database (prior to restore)

------------------------------------------

--- Spid Psycho Killer ---

--- Version 1.0 ( Qu'est-ce que c'est) ---

--- Corey Schafer (c) 2019 ---

--- DesigningDigits.Com ---

--- Corey@CoreySchafer.com----

-------------------------------------------

-- cursor

SET NOCOUNT ON;

DECLARE @Spid int, @sqlString varchar(max), @PrintCommand int, @ExecuteCommand int

set @PrintCommand = 1

set @ExecuteCommand = 1

DECLARE spidKillaCursor CURSOR FOR

select spid from sys.sysprocesses where DB_NAME(dbid) = 'DATABASENAME'

OPEN spidKillaCursor

FETCH NEXT FROM spidKillaCursor INTO @spid

WHILE @@FETCH_STATUS = 0

BEGIN

set @sqlString = 'kill ' + convert(varchar, @spid)

if @printCommand = 1 begin

print @sqlString

end

if @ExecuteCommand = 1 begin

exec (@sqlString)

end

FETCH NEXT FROM spidKillaCursor INTO @spid

END

CLOSE spidKillaCursor;

DEALLOCATE spidKillaCursor;

uSE [master]

RESTORE DATABASE DATABASENAME FROM DISK = N'G:\DATABASENAME.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5

GO