MsSQL Database: Drop connections

If you need to restore a Microsoft SQL Database, you might run into the problem that there are still users connection to the database.

It’s not easy to drop those connections… lots of info that I find on google use

ALTERDATABASE DB_NAME SET SINGLE_USER WITHROLLBACK IMMEDIATE
--do stuff
ALTERDATABASEDB_NAME SET MULTI_USER

And this doesn’t always seem to work on SQL2008 or SQL2012.

But… here’s a handy script that I found on Stackoverflow:

Replace the DB_Name variable with the name of your DB for which you want to drop the connection:

USE master
GO

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'DB_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END