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.

Solarwinds
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)

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

Related Posts

535 Comments. Leave new

  • Please let me know if i want to download the backup from server like IP and store into my laptop (i.e server is located in mumbai and Im staying in pune ) own dedicated server how to do it from SQL script or any special software is there …

    Regards
    Mayur

    Reply
    • If there are connected via VPN, you can directly run a restore command. Otherwise copy it to laptop and restore it there seperately

      Reply
  • Thanks a lot it worked for me..

    If your getting restoration failed while trying to restore the backup.

    Error msg: “restore failed for server microsoft.sqlserver.smo” Then use the below solution in MS SQL 2005.

    The below is the query i used :

    RESTORE FILELISTONLY
    FROM DISK = ‘C:DBfilename.bak’
    GO

    “”once u execute the above query you get the logical filename” use this logical filename in the below query.”

    RESTORE DATABASE TestDB
    FROM DISK = ‘C:DBfilename.bak’
    WITH REPLACE,MOVE ‘Logicalfilename’ TO ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTest_DB.mdf’,
    MOVE ‘Logicalfilename_log’ TO ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTest_DB.ldf’,
    MOVE ‘sysft_ifindexused_index’ TO ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTest_DB.HRD’


    Regards
    Narashiman K Iyengar

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

    RESTORE FILELISTONLY FROM DISK = ‘D:BACKUP.BAK’
    Go

    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

    Reply
  • Hi Pinal Dave,

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

    Refering to the original script:
    =========================
    RESTORE DATABASE YourDB
    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

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

    Reply
  • Your posts are really helpful.

    This is my implementation of the commands
    RESTORE DATABASE MyFiles
    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

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

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

    Thanks,
    Yash

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

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

    Thanks,
    Shweta

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

    Reply
  • 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:

    ——————————
    ADDITIONAL INFORMATION:

    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:

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

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

    Reply
  • 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!!

    Reply
  • excuse me sir

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

    Reply
  • 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,

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

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

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

      Reply
  • 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 ?

    Regards

    Reply
  • Anthony Jackson
    November 1, 2011 8:46 pm

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

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

      Reply
  • 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!!

    Reply

Leave a Reply

Menu