Kill All Connections To Your SQL Server Database

Sometimes you need to restore a database and put it in single user mode but you can't because people are still using it. I will show you 2 ways to accomplish that
The first way is to loop through the sysprocesses table and kill all the processes one by one. The second way is to do an alter statement on the DB


--loop through the sysprocesses
DECLARE @sysDbName SYSNAME
SELECT @sysDbName = 'northwind'

SELECT IDENTITY(int, 1,1)AS ID,spid
INTO #LoopProcess
FROM master..sysprocesses
WHERE dbid = DB_ID(@sysDbName)


DECLARE @SPID SMALLINT
DECLARE @SQL VARCHAR(255)
DECLARE @MaxID INT, @LoopID INT

SELECT @LoopID =1,@MaxID = MAX(ID) FROM #LoopProcess

WHILE @LoopID <= @MaxID
BEGIN
SELECT @SPID = spid FROM #LoopProcess WHERE ID = @LoopID
SELECT @SQL = 'KILL ' + CONVERT(VARCHAR, @SPID)

EXEC( @SQL )

SELECT @LoopID = @LoopID +1
END

DROP TABLE #LoopProcess


--alter the DB by making it single user (all transaction will be rolled back)
ALTER DATABASE northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--do your restore here


-- Make the DB multi user again
ALTER DATABASE northwind SET MULTI_USER