By

Restore Backed Up Database in MS SQL Server Using Transact-SQL

The following code will restore a database from a backup file in MS SQL Server. Normally you would use Management Studio to do this, but this might fail if the database needs to be in single user mode to restore. The code below will restore the database as a single user, then put it back in multi-user mode after its done.

—[ Transact-SQL Code ]——————–

-- Set database as single user and restore from backup.
USE master
ALTER DATABASE <your_database>
 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE <your_database>
 FROM DISK = 'D:\Databases\YourDatabase.bak'
 WITH REPLACE
GO
-- Set database back to multi user.
ALTER DATABASE <your_database> SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.