Exclusive access could not be obtained because the database is in use.

The ability to restore a SQL Server database over and over can sometimes be handy in a test scenario, for instance when you need to test  a schema-update for autodeployment through DbUp/Octopus – or you otherwise need to have your database in a known state prior to testing.

However this is quite often blocked because the database is in use, resulting in the above message.

My solution to this is to script the restore to run in single user mode like so:

Code Snippet
  1. USE [master]
  2. ALTER DATABASE [BenchmarkDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  3.   RESTORE DATABASE [BenchmarkDB] FROM  DISK = N'D:\BenchmarkDB.bak' WITH  FILE = 1,  MOVE N'BenchmarkDB_log' TO N'D:\Data\BenchmarkDB.ldf',  NOUNLOAD,  REPLACE,STATS = 5
  4. ALTER DATABASE [BenchmarkDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE
  5. GO

 

Update 2014-09-01:

To achieve the same in Management Studio then go to Options and check this box:

image

To check whether your database got correctly reset to Multiuser Mode, go to Database Properties > Options, and look at the “State” subgroup, “Restrict Access” item (you’ll need to scroll down to the bottom of the list to see it):

image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s