One issue I’ve run across frequently during development is restoring a database to a newer state. Often, when I want perform the restore, there are active connections to my development database, so restoring will fail.

Of course, StackOverflow had the answer to this, but I’ve searched for the solution to this problem enough times where it made sense for me to finally write it down.

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

User AlexK posted this excellent solution:

For MS SQL Server 2012 and above

USE [master];

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('MyDB')

EXEC(@kill);

For MS SQL Server 2000, 2005, 2008

USE master;

DECLARE @kill varchar(8000); SET @kill = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'  
FROM master..sysprocesses  
WHERE dbid = db_id('MyDB')

EXEC(@kill);