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.

FROM DISK = 'D:\BackUpYourBaackUpFile.bak'

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

----Make Database to single user Mode

----Restore Database
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
If error occurs please execute following command it will convert
database in multi user.*/

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

  • hello,

    i need some help here with sql server on win 2008 server running

    i have receive a BACKUP.BAK and i would like to restore it.

    i did a new DATABASE and i tried to run


    came out with this error

    Msg 3242, Level 16, State 1, Line 1
    The file on device ‘D:BACKUP.BAK’ is not a valid Microsoft Tape Format backup set.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE FILELIST is terminating abnormally.

    how do i solve this? thanks

  • Hi Pinal Dave,

    I have some amateur sort of questions. Kindly clear them. Thanks

    Refering to the original script:
    FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
    WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
    MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.ldf’

    1. If i had multiple log files when i took the backup then on restore operation, will multiple files be created or a single ldf and single mdf file will be created in Database? (i presume 01 mdf and 01 ldf for the said database be created.)

    2. When we create a backup of a database that is spanned on multiple mdf/ndf and ldf files, then is the structure of the resulting backup (.bak) file same as source files (does .bak files has single data and signle log file?)?

    Reply soon

  • Dear Mayur.
    Ask a question Comprehensibly. Exatly what do u want.

  • Your posts are really helpful.

    This is my implementation of the commands
    FROM DISK = ‘E:\My Documents\SQL\storeMyFiles-Database\StoreMyFiles.bak’
    WITH MOVE ‘MyFiles’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\myFiles.mdf’,
    MOVE ‘MyFiles_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\myFiles_log.ldf’

    and this is the result
    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 have seen that you need 2 files which I don’t have even though the backup file seem to have the .mdf and .ldf files, however I have not seen the problem mentioned here.

    Maybe you can help. Thanks in advance

  • Hi Pinal
    I would like to know how to take backup in sql server 2005 from shared server. I have knowledge about ordinary backup and restore but from web server there is no option. Your help would be helpful.

  • Is there any way to restore the Stored Procedure in SQL 2005 ??


  • Hi,
    How to kill all active connections to DB using SQL script…

  • Hi All,

    I am restoring the database using command line (SQL Express), when the restoration is successful will get the message as restore database successfully processed.

    If the restoration fails for any of the reason, it gives the message as restoration completed abnormally and it gives the “state” option/parameter also.. here how do i capture this state option ?

    Please help me ..


  • hi All

    I want restore file database of sql 2005 express into sql 2005 developer.
    But it not execute. How can I use it? Please help me! Thanks

  • hi,
    I want restore database of sql 2005 while doing this i got following error:
    TITLE: Microsoft SQL Server Management Studio

    Restore failed for Server ‘HOME-B00DD89814’. (Microsoft.SqlServer.Smo)

    For help, click:


    System.Data.SqlClient.SqlError: The media set has 3 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)

    For help, click:

    • It means that the backup file has three related files. You need to specify all these three files to restore it properly

      • than for reply .
        sir i copy data base from my friend laptop.then i try to restore it on desktop.i first copy the data base to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup.
        as mention i go throw all restore procedure. according to me we have only one database whose backup i have copied. but for safety i save it on different drives (on my desktop) then after error mas i delete all .kindly plz help me urg.
        plz help me.

  • hi,
    thanks for your 1st mail.
    sir is it possible that only one database has three related files.
    how to check that how much files are related to each other and how to take such database file while restoring.
    sir plz reply me early as possible .i am student not having much about this sql server.

  • sir ,i execute this specid\fuc query. there i m getting DHW ma database file and its log file havig properties as TYPE: D for database and L for Log .
    plz guide me further , waiting for ur Reply..thanks in advance!!

  • excuse me sir

    how to restore multiple backups at once using sql script(t-sql)

  • Hello SIr,
    thanks for your assist,
    May you tell me,
    How to schedule backup time for database ?
    ex:- suppose i would like to backing up my database 2 times per day, at 10 am and 6:00 pm , how should i schedule timing for database backup,

    • You can do it via SQL Server Agent. Create a new job and schedule it as you wish. The schedule section provides an access to schedule it. Goto section occurs every and select 8 and specify starting and ending time as 10 am and 6 pm respectively

  • I want to restore/replace the new DB on any other server’s old DB with same name

    • 1 Copy the backup file into new server’s location
      2 Use this command in new server

      Restore database db_name from disk=’file_path’ with replace

  • Alberto Sandoval
    October 28, 2011 5:26 am

    Hi All,

    Is possible create a task on SQL 2008 R2 for the DB can be restore automatically?

    Any Advice ?


  • Anthony Jackson
    November 1, 2011 8:46 pm

    Hello, How can I restore 100 + Database with just one script ?

    • You can have all of them in a folder and write a query to get file names using xp_cmdshell and dynamically build restore database statements and execute them

  • Hello, I need a little help with a restore process,

    I have this script in my system:

    RESTORE DATABASE [BasePrincipal] FROM DISK = N’C:\Sistema\backup.bak’ WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    It work perfect in Windows XP, but not in Windows 7…

    The message is:
    Error al buscar el archivo ‘C:\Archivos de programa\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Basesistema.mdf’ en los directorios, error del sistema operativo: 3(failed to retrieve text for this error. Reason: 15105)

    What can I do?
    Thanks for your help!!

  • Hi
    I have a Sql server .bak file created in Sql 2005.

    I don’t have Sql server 2005 installed. Recently i have installed Sql server 2008
    When i am trying to restore the .bak file. I am getting the following error

    Restore failed for Database ‘BMTool’ microsoft.sqlserver.smo

    –create database permission denied in database ‘master’

    Why is it pointing to ‘master’ database when i am trying to create a new database with the bak file.

    Please suggest me the steps as i am new to sql server.

  • Hi,
    I am having 9 database under once instance, need to write script to take backup. on network mapped drive, I have limited knowledge of scripting request your help to guide.




Leave a Reply