Kill connections to resote DB - SQL Server

Comments (2) | Share

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