Kill connections to resote DB - SQL Server

The following code is from Ralph Whitbeck's blog. This is really a "cut and save" for me :)

When restoring an SQL Server Database, you'll often need to kill existing connections to it, before starting the task.

Change databaseName To the actual database name.

Use Master
Go

Declare @dbname sysname

Set @dbname = 'databaseName'

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End

 

posted @ Wednesday, February 24, 2010 11:50 AM
Print

Comments on this entry:

# re: Kill connections to resote DB - SQL Server

Left by Morteza at 8/22/2010 11:45 AM
Gravatar
Thanks, nice tip!

# re: Kill connections to resote DB - SQL Server

Left by Khaled EL Sheikh at 10/28/2013 8:15 AM
Gravatar
thanks indeed for you help

Your comment:



(not displayed)

 
 
 
 

Live Comment Preview: