Thursday, January 31, 2013

Restoring a DB when the database is “In Use”

We’ve all run into this issue before. We have a backup of a database that we need to restore into a DEV/QA environment, but when we attempt to restore, we get the “System.Data.SqlClient.SqlError:  Exclusive access could not be obtained because the database is in use

This error can be very frustrating to get around and a person can spend a LONG time trying to kill all of the attached queries/threads. I know I’ve (almost) lost my sanity on more than 1 occasiaion trying to do this.

The good news is that I’ve found a simple/easy way to restore a backup when the database is in use

Steps:

  • Setting the database to single user (you!)
  • restore the backup
  • set it back to multiuser

Here is the SQL Script I use.

image

Simple and fast. Enjoy!

No comments:

Post a Comment