SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

In this blog post we are going to learn how to restore database backup using T-SQL script. We have already database which we will use to take a backup first and right after that we will use it to restore to the server. Taking backup is an easy thing, but I have seen many times when a user tries to restore the database, it throws an error.

SQL SERVER - Restore Database Backup using SQL Script (T-SQL) 44-800x450

Step 1: Retrive the Logical file name of the database from backup.

RESTORE FILELISTONLY
FROM DISK = 'D:\BackUpYourBaackUpFile.bak'
GO

Step 2: Use the values in the LogicalName Column in following Step.

----Make Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE YourDB
FROM DISK = 'D:\BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:\DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:\DataYourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO

Watch a 60 second video on this subject

Let me know what you think of this blog post and if you use the T-SQL scripts displayed in this blog post, just let me know if it requires any improvement.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Next Post
SQL SERVER – Interesting Observation – Using sqlcmd From SSMS Query Editor

Related Posts

535 Comments. Leave new

  • SSMC Restore on *.bak does not seem to produce the same as TSQL Restore Command in the query window and or in C# using TSQL and C# using SMO.

    Can you offer a possible cause, even better a solution?

    As in a table I am checking (user logs) via TSQL/SMO will have hours of missing entries on completion where using the same *.bak file with SSMC returns the original set.

    Reply

Leave a Reply

Menu