Script to kill all connections to a database

When restoring/moving databases its a pretty common requirement to kill all the connections to the database in question otherwise you can't get a lock on it. Killing all the connections to a database can be a pain though if you have to use the UI. This script is much quicker and can be a time saver. If you’re super keen you could modify the script to make it a stored procedure so it’s always ready to go. If you do want to use the UI here is a blog post on how to do it.

--
-- Kill connections to a given sql server database
--

declare @execSql nvarchar(MAX), @databaseName varchar(100)
set @databaseName = '<your database name here>'

set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
and
DBID <> 0
and
spid <> @@spid
exec(@execSql)