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:

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

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *