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.

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

1 2 3

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

----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.

Interview Question of the Week #012 - Steps to Restore Bak File to Database
Here is the question, I was asked the other day at the conference. Question: If I have a .bak file and I want to restore it database what are the various steps involved in it. Assume that I do not know much about that backup file. Answer: I love this question,…

March 22, 2015
In "SQL"

SQL SERVER - FIX: Msg 3102, Level 16, State 1 - RESTORE cannot process database 'Name' because it is in use by this session
June 6, 2015
In "SQL"

SQL SERVER - The Log Scan Number Passed to Log Scan in Database 'master' is not Valid
January 13, 2017
In "SQL"

t sql restore database with move, t sql restore database with replace, tsql restore database from bak file
Source: http://docphy.com/technology/computers/software/sql-server-restore-database-backup-using-sql-script-t-sql.html