Kill All Connections To A Database (prior to restore)
- Corey Schafer
- Oct 11, 2019
- 1 min read
------------------------------------------
--- 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
Comentários