Technical Article

Kill Spids and Restore Database

,

This script will remove all the spids from a particular and allow you restore a database to a different location. The path of dump can be local or remote.

It is very useful for quickly restoring a copy of production for testing purposes.

-- --------------------  KILL SPIDS AND RESTORE DATABASE ------------------------------
/*

John McGinty, 2005.

Kill Spids and Restore a database with a poentially different database name and 
filegroup Locations.  Useful for moving and restoring database between different 
environments. 

*/
USE MASTER 
DECLARE @KillSpid int , @CMD nvarchar (20) DECLARE cur_kill CURSOR FOR SELECT SP.spid, SD.name -- DATABASE NAME GOES HERE
FROM SYSPROCESSES SP JOIN SYSDATABASES SD ON SP.dbid = SD.dbid  WHERE SD.name = 'MyDatabase'
OPEN cur_kill  FETCH NEXT FROM cur_kill  INTO @KillSpid  ,@CMD WHILE @@FETCH_STATUS = 0 --------------------------
BEGIN  SET @CMD = 'KILL ' +  CAST ( @KillSpid  as Varchar(3))  EXECUTE sp_executesql @CMD 
PRINT CAST ( @KillSpid  as Varchar(3)) +  ' SPID KILLED ' FETCH NEXT FROM cur_kill  INTO @KillSpid  , @CMD 
END  CLOSE cur_kill DEALLOCATE cur_kill

RESTORE DATABASE MyDatabase

-- FROM DISK = 'D:\DBDumps\MyDatabase.bak'
           FROM DISK = '\\MyServer\DBDumps\MyDatabase.bak'

   WITH MOVE 'MyDatabase_Data' TO 'd:\SQL\MSSQL\data\MyDatabase_Data.MDF',
   MOVE 'MyDatabase_Log' TO'd:\SQL\MSSQL\data\MyDatabase_Log.LDF' , REPLACE

Rate

Share

Share

Rate