Restoring A Database Without Encountering A Locking Issue

Microsoft SQL ServerI 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.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

1 thought on “Restoring A Database Without Encountering A Locking Issue

Leave a Reply

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