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:
DECLARE @dbname sysname DECLARE @spid int SET @dbname = 'myAppsDB' SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL BEGIN EXECUTE ('KILL ' + @spid) SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid END GO
ALTER DATABASE @dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO
RESTORE DATABASE @dbname FROM DISK = N'D:\DBBackups\myAppsDB.BAK' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10 GO
That did it, and to the victor belong the spoils...