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.

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

  • 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
  • Hi Pinal, Could you please explain me what media family is? Actually I got to know that we can restore a database from multiple .bak files if these are in different media families. Hence would like to know what it is.

    Reply
  • Hi Pinal,

    Do you have an application you could recommend that would replace scripts running on windows sever 2003 which move data between applications and databases? The server is being upgraded to 2008 or 2012 R2 so would need to be compatible.

    It would need the following:
    1. ability for remote systems to send files using the ftp & ftps/tls protocols (to be a server/listen for connections)
    2. ability to transfer files to/from various systems using the following protocols (to be a client/initiate connections):
    • ftp
    • ftps/tls
    • sftp/ssh
    • unc

    3. schedule transfer of files
    4. archive files transferred
    5. manage archive/deletion of content
    6. error handling in the event of failed transfers
    7. resend files previously transferred successfully
    8. workflow logic to cater for complex business requirements
    9. transfer files to appropriate destination system based on file name/extension
    10. schedule transfers based on the date of the generated file
    11. ability to zip multiple files and create a manifest of contents for remote system
    12. notification to interested parties

    Regards,

    Hass.

    Reply
  • I am trying to put together a script to restore a database from a different server with different credentials is this possible I am a tad lost on this

    RESTORE DATABASE dbname
    FROM DISK = ‘\\different server\folder\dbname.bak’
    (Where can I state my username and password to access “Different server”)

    GO

    Reply
    • you may want to map the drive within SQL using xp_cmdshell net use command. I wrote a blog earlier. search on my blog.

      Reply
  • Moe Lone Nyo
    June 4, 2015 1:42 pm

    hi Sir,
    I run the below command,

    RESTORE FILELISTONLY
    FROM DISK = ‘C:\Temp\lmdw.bak’

    ALTER DATABASE TE
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE

    Use master
    RESTORE DATABASE TE
    FROM DISK = ‘C:\Temp\lmdw.bak’
    WITH MOVE ‘TE’ TO ‘D:\Database\Conversion_DB\TE.mdf’,
    MOVE ‘TE_log’ TO ‘D:\Database\Conversion_DB\TE_log.ldf’

    show error

    Msg 3154, Level 16, State 4, Line 3
    The backup set holds a backup of a database other than the existing ‘TE’ database.
    Msg 3013, Level 16, State 1, Line 3
    RESTORE DATABASE is terminating abnormally.

    Reply
  • Hi Sir,
    I run the below commands,
    RESTORE FILELISTONLY
    FROM DISK = ‘C:Templmdw.bak’

    ALTER DATABASE TE
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE

    Use master
    RESTORE DATABASE TE
    FROM DISK = ‘C:Templmdw.bak’
    WITH MOVE ‘TE’ TO ‘D:DatabaseConversion_DBTE.mdf’,
    MOVE ‘TE_log’ TO ‘D:DatabaseConversion_DBTE_log.ldf’

    show error

    Msg 3154, Level 16, State 4, Line 3
    The backup set holds a backup of a database other than the existing ‘TE’ database.
    Msg 3013, Level 16, State 1, Line 3
    RESTORE DATABASE is terminating abnormally.

    Please anyone help me
    Thanks

    Reply
  • Hi Sir,
    I restore the .DAT file into sql server 2008R2 with command
    RESTORE FILELISTONLY
    FROM DISK = ‘C:TempCUS-INFO.DAT’

    Use master
    RESTORE DATABASE TE
    FROM DISK = ‘C:TempCUS-INFO.DAT’
    WITH MOVE ‘TE’ TO ‘D:DatabaseConversion_DBTE.mdf’,
    MOVE ‘TE_log’ TO ‘D:DatabaseConversion_DBTE_log.ldf’

    same error show

    Msg 3241, Level 16, State 0, Line 2
    The media family on device ‘C:TempCUS-INFO.DAT’ is incorrectly formed. SQL Server cannot process this media family.
    Msg 3013, Level 16, State 1, Line 2
    RESTORE DATABASE is terminating abnormally.

    Please give me any suggestion for restore the .DAT file into sql server 2008R2
    Thanks.

    Reply
  • Hi Pinal,
    I have tried the steps you have suggested but i get the below error. I am working on SQL server 2008 R2. Please suggest a way restore a db from a backup file from other db.

    Msg 1834, Level 16, State 1, Line 1
    The file ‘D:\SQLDATA\Accellos_Test.mdf’ cannot be overwritten. It is being used by database ‘Accellos_Test’.
    Msg 3156, Level 16, State 4, Line 1
    File ‘AccellosWMS’ cannot be restored to ‘D:\SQLDATA\Accellos_Test.mdf’. Use WITH MOVE to identify a valid location for the file.
    Msg 1834, Level 16, State 1, Line 1
    The file ‘D:\SQLLog\Accellos_Test.ldf’ cannot be overwritten. It is being used by database ‘Accellos_Test’.
    Msg 3156, Level 16, State 4, Line 1
    File ‘AccellosWMS_log’ cannot be restored to ‘D:\SQLLog\Accellos_Test.ldf’. Use WITH MOVE to identify a valid location for the file.
    Msg 3119, Level 16, State 1, Line 1
    Problems were identified while planning for the RESTORE statement. Previous messages provide details.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    Reply
  • If your doing a refresh use option with replace.

    Reply
  • Lynn Lahman
    June 27, 2016 2:23 am

    This is a great example and I have used this very same method to restore/rename a database successfully. I just have one question if you would be so kind…If (due to some odd technical/hardware issues) I was trying to accomplish this very same objective but had a transaction log backup as well in this backup, how do I apply the (any) transactions from the log backup while renaming/moving the source database? In what sequence (and how) does the transaction log get restored?

    Thanks in advance! You have been a favorite writer of mine for some time. Thanks for all the tips!

    Reply
  • Works Beautifully on linux!

    Reply
  • Satyendra Vir Singh
    November 27, 2017 1:26 pm

    D:DB_DUMPS
    cannot access the specified path or file on the server.verify that you have the neccessary security privileges and that the path or file exits.

    Restore the database and generated the DB_DUMPS error in database file and does not restore.

    Reply
  • Hi Sir..
    Please Help me to solve my problem.

    Problem: When i restore database using the query that ->RESTORE Database mydb FROM DISK = ‘C:\DbBackUp\mydb.bak’ ;
    i got an error message

    Msg 3201, Level 16, State 2, Line 1
    Cannot open backup device ‘C:\DbBackUp\mydb.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.

    Note: 1. mydb database is not exist in system.database
    2. mydb.bak is exist in C:\DbBackUp folder.

    How to solve this error ? ..

    THANKS IN ADVANCE…

    Reply
  • Dear Sir,

    Sir I need to create database backup script with data from sqlcmd. in sql server 2008 r2. can you please help me..?

    Reply
  • Hi Sir!! can you give me query that restore the latest Backup from multiple multiple backup files on the same file, Please answer it.

    Reply
  • I use this script and make one stored procedure for restore database. I will takes almost 1.30 hours for restore if i execute the query. But when i create maintenance plans and call T-SQL block for calling this stored procedure and set my schedule jobs it gives me error. can you help me to figure it out.

    Job History Error Below:

    Date 28/11/2019 10:50:00 AM
    Log Job History (Job.WeeklyRestoreDB)

    Step ID 1
    Job Name Job.WeeklyRestoreDB
    Step Name WeeklyRestoreDB
    Duration 00:01:03
    Sql Severity 0
    Sql Message ID 0
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0

    Message
    Executed as user: SQL2017TEST$. Microsoft (R) SQL Server Execute Package Utility Version 14.0.1000.169 for 64-bit Copyright (C) 2017 Microsoft. All rights reserved. Started: 10:50:01 AM Progress: 2019-11-28 10:50:01.46 Source: {E647A2F4-3D2E-4195-9478-8F282999B0A3} Executing query “DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp…”.: 100% complete End Progress Error: 2019-11-28 10:51:01.67 Code: 0xC002F210 Source: Execute T-SQL Statement Task Execute SQL Task Description: Executing the query “EXEC [master].[dbo].[JOB_WeeklyDBBackup]” failed with the following error: “Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Configuration option ‘remote query timeout (s)’ changed from 600 to 0. Run the RECONFIGURE statement to install.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly. End Error Warning: 2019-11-28 10:51:01.67 Code: 0x80019002 Source: WeeklyRestoreDB Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:50:01 AM Finished: 10:51:01 AM Elapsed: 60.938 seconds. The package execution failed. The step failed.

    How to do i resolved this?

    Reply
  • Satyabrata Samantaray
    March 25, 2020 4:40 pm

    Thank you. it worked for me

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version