Database Restore Failed: Could Not Obtain Exclusive Access

Database Restore Failed: Could Not Obtain Exclusive Access

Close My Connections, Please

I was “testing” a bit hard today and broke the WideWorldImporters database. A valiant effort was made to restore the database to a prior known good working state, however, the UI in SSMS had other plans. It even had the nerve to grey out the option to close existing connections… and if my open tabs were any indication, I had many, many existing connections.

Fortunately, this was an easy fix:

  1. Toggle one of the checkboxes in the Restore Database dialog box to enable the Script dropdown
  2. Load the restore script into a new query window
  3. Add a line of code to the top of the script to set the database in single-user mode and rollback any existing transactions

Overcome the UI

Single-User Mode with Rollback

ALTER DATABASE [WideWorldImporters] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

 

Results