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)

SQL Error Messages, SQL Restore, SQL Scripts, SQL Server, SQL Utility
Next Post
SQL SERVER – Interesting Observation – Using sqlcmd From SSMS Query Editor

Related Posts

535 Comments. Leave new

  • 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’

    when i do this, i’m getting error msg as

    Msg 3132, Level 16, State 1, Line 1
    The media set has 2 media families but only 1 are provided. All members must be provided.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    i dont have the db so that i can backup again the db and all i have is just the Backup of my database… everytime i try to restore my db, it shows the same error..

    can anyone help me plzzz…

    Reply
  • hi pinal..
    Iam getting the below error while restoring my backup.please help me.
    Msg 3201, Level 16, State 2, Line 1
    Cannot open backup device ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER2008\MSSQL\Backup.bak’. Operating system error 2(The system cannot find the file specified.).
    Msg 3013, Level 16, State 1, Line 1
    RESTORE FILELIST is terminating abnormally.

    Reply
  • mordhwaj chauhan
    November 24, 2012 1:05 pm

    Thank You…

    Reply
  • Dave,
    We recieve monthly database updates in from of MDF and LDF files, Can I restore database from MDF and LDF files and replace the old ones with new ones?
    If yes can you please post script to automate it.

    Regards,
    Malii

    Reply
  • Somebody please Help!

    Reply
  • What is the Difference between:
    >BACKUP DATABASE Database_Name TO DISK = ‘C:\.bak’;

    AND

    >BACKUP DATABASE Database_Name TO DISK = ‘C:\.bak’ with Format;

    Reply
  • COPY .MDF FILE AND .LDF FILE FROM C:\PROGRAM FILES\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
    TO THE MACHINE WHERE YOU WANT TO RESTORE IT
    THEN SIMPLY GO TO SQL ENTERPRISE MANAGEMENT STUDIO AND ATTACHED THOSE .MDF FILE AND SQL WILL AUTOMATICALLY CREATE A NEW DATABASE IN 2008 FROM OLD 2000 DATABASE.

    Reply
  • Hi Sir
    i want to restore my database on my desire location by sql query
    please tell me how can do that…
    Thanks

    Reply
  • nce

    Reply
  • Parmod kumar
    May 27, 2013 2:31 pm

    Hello Sir

    i am trying to take database offline, but it in processing from more than last one hour..

    Reply
  • Msg 3154, Level 16, State 4, Line 1
    The backup set holds a backup of a database other than the existing ‘mydb’ database.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.
    how can i resolve this.

    Reply
  • sir ,
    i am applying this code on my sql but it dose not work and gives a error
    plz solve error and reply me on my email address

    my code is this:-

    RESTORE FILELISTONLY
    FROM DISK = ‘E:new.bak’
    GO

    ALTER DATABASE new
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE

    —-Restore Database
    RESTORE DATABASE new
    FROM DISK = ‘E:new.bak’
    WITH MOVE ‘new’ TO ‘E:new.mdf’,
    MOVE ‘new’ TO ‘E:new.ldf’

    and error is this :-
    Msg 3154, Level 16, State 4, Line 7
    The backup set holds a backup of a database other than the existing ‘new’ database.
    Msg 3013, Level 16, State 1, Line 7
    RESTORE DATABASE is terminating abnormally.

    Reply
    • Mukesh,

      Not sure if you’ve sorted this but you seem to have missed off the backslash in the file path. i.e. e:new.bak should be E:new.bak.

      Reply
  • hi ,
    my database is located in other system. am connecting other user mode and take that db back up … but its showing error .. pls help me …
    BACKUP DATABASE db TO DISK=’d:\adw.bak’

    Reply
  • SUBRAT KUMAR RAUTARA
    June 29, 2013 8:26 am

    Thanks Pinal for this Blog.
    It worked nicely.

    Reply
  • Hii Pinal

    Some times our restore takes too long time appox 8 hours to restore 160 GB data

    and Manytimes it hardly takes 3 hours

    using sqlserver R22008

    kindly tell a solution

    thanks

    Reply
  • Dear All,
    i would like to restore a set of databases (more than 100) and i would like to create a script to do that.

    How i can create a script to:
    1. create database base on the name of the bck file
    2. select the bck file
    3. restore database (and define a different location of mdf, ndf and ldf file)

    Thanks for your support.

    Reply
  • you are really the best. This help me iamd save my work. Be carfull

    Reply
  • The media family on device ‘E:SHD150.bak’ is incorrectly formed.

    Reply
  • USE master RESTORE DATABASE FILELISTONLY HighTermAccount2013_02082014_143.bak FROM Disk = ‘c:\BackupScheduler\DataBase Backup\HighTermAccount2013_02082014_143.bak’

    Error :
    Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near ‘.’.

    Reply
    • USE master
      go
      RESTORE FILELISTONLY
      FROM Disk = ‘c:\BackupScheduler\DataBase Backup\HighTermAccount2013_02082014_143.bak’

      Reply
  • Hi Sir,

    I am Senthilkumar,I have one doubt in how to get the Backup and Restore DB in TFS using SQL Query Format.Plz help me for solve this .

    Thank you sir

    Reply

Leave a Reply