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)
535 Comments. Leave new
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.
@Princy – https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms178062(v=sql.110)
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.
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
you may want to map the drive within SQL using xp_cmdshell net use command. I wrote a blog earlier. search on my blog.
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.
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
You ned to use “REPLACE” word in restore command because you are overwriting.
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.
backup taken from higher version of SQL can’t be restored on lower version.
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.
If your doing a refresh use option with replace.
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!
Works Beautifully on linux!
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.
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…
Dear Sir,
Sir I need to create database backup script with data from sqlcmd. in sql server 2008 r2. can you please help me..?
Hi Sir!! can you give me query that restore the latest Backup from multiple multiple backup files on the same file, Please answer it.
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?
How big is your backup file?
Thank you. it worked for me
SSMC Restore on *.bak does not seem to produce the same as TSQL Restore Command in the query window and or in C# using TSQL and C# using SMO.
Can you offer a possible cause, even better a solution?
As in a table I am checking (user logs) via TSQL/SMO will have hours of missing entries on completion where using the same *.bak file with SSMC returns the original set.