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
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…
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.
It means that there is no such file exists at the path you specified. Make sure to use the correct path
Thank You…
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
Somebody please Help!
What is the Difference between:
>BACKUP DATABASE Database_Name TO DISK = ‘C:\.bak’;
AND
>BACKUP DATABASE Database_Name TO DISK = ‘C:\.bak’ with Format;
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.
Hi Sir
i want to restore my database on my desire location by sql query
please tell me how can do that…
Thanks
nce
Hello Sir
i am trying to take database offline, but it in processing from more than last one hour..
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.
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.
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.
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’
Thanks Pinal for this Blog.
It worked nicely.
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
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.
you are really the best. This help me iamd save my work. Be carfull
The media family on device ‘E:SHD150.bak’ is incorrectly formed.
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 ‘.’.
USE master
go
RESTORE FILELISTONLY
FROM Disk = ‘c:\BackupScheduler\DataBase Backup\HighTermAccount2013_02082014_143.bak’
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
TFS backups are used using their own interface. It uses STOP AT MARK command.