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.
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);