Restoring A Database Without Encountering A Locking Issue

I periodically have problems when trying to restore a GP company database over the Test database as SQL reports that the database is currently in use.

One way of resolving is to restart the SQL Server but this is only possible on a stand alone test system which is not being used by other people but this approach is overkill.

The better solution is to change the database to single user, restore the database and change the database back to multiple user. This can be done manually through SQL Server Management Studio but is far easier done via a SQL script.

The below example is restoring over the top of the Fabrikam TWO database (with the elements you would need to change highlighted in yellow);


USE master
GO

ALTER DATABASE [TWO]
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
RESTORE DATABASE
[TWO] FROM
DISK = N'C:\SQL Data\SQL2008R2\Backup\TWO.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
ALTER DATABASE [TWO] SET MULTI_USER
GO

As always don’t run the script unless you’re happy dealing with SQL Server Management Studio and take caution that you don’t overwrite the wrong database.

One comment on “Restoring A Database Without Encountering A Locking Issue

  1. Pingback: Interesting Findings & Knowledge Sharing » Restoring A Database Without Encountering A Locking Issue

Leave a Reply

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

*

* Copy This Password *

* Type Or Paste Password Here *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>