SQL Server: Surefire Restore Method for Connection Hammered Databases

Recently, I had a database go into the dreaded SUSPECT mode after a failed byte-level replication. Once I brought it into EMERGENCY mode, an unknown, automated program immediately connected to it from several different servers.

Killing the connections and setting the db to single-user mode only created more headaches because this meant I couldn't be lazy and perform the RESTORE from SSMS; I'd keep receiving these errors: Exclusive access could not be obtained because the database is in use or The database is in single-user mode, and a user is currently connected to it.

I was determined, however. This is a battle I shall not lose! Ultimately, I ended up running the following code, which allowed for a successful restore:

 1DECLARE @dbname sysname
 2DECLARE @spid int
 3SET @dbname = 'myAppsDB'
 4SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db\_id(@dbname)
 5
 6WHILE @spid IS NOT NULL
 7     BEGIN
 8          EXECUTE ('KILL ' + @spid)
 9          SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db\_id(@dbname) AND spid > @spid
10     END
11GO
12
13ALTER DATABASE @dbname SET  SINGLE\_USER WITH ROLLBACK IMMEDIATE
14GO
15
16RESTORE DATABASE @dbname FROM  DISK = N'D:\\DBBackups\\myAppsDB.BAK' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
17GO

That did it, and to the victor belong the spoils...

Update (2025): The approach above still works, but master.dbo.sysprocesses is deprecated in modern SQL Server. If you prefer DMVs, you can identify and end sessions using sys.dm_exec_sessions and sys.dm_exec_requests. Often, setting the database to single-user with WITH ROLLBACK IMMEDIATE (as shown) is enough to clear connections before a restore; keep the KILL loop only if something keeps reconnecting immediately.