Kill connections to restore a SQL Database

Yes, we are not perfect when doing development. Sometimes, after running some bad scripts or code, we need to restore our development database. Problem is, since you are connected to the database, the restore will fail. What you need to do is kill the connections to the database. Then you can restore it.

First in SQL Management Studio click on ‘New Query’ and make sure the drop down is set to master. Now run this SQL Script.

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

Make sure you swap out ‘databaseName’ with the database you want to kill the connections to and restore.

Click Here to Leave a Comment Below 0 comments

Leave a Reply: