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 ,
I am restoring 9 dbs in a standby server (sql2000), everything is working fine except that I cant restore a certain db and I am wondering why?please help thanks.
My stored procedure:
….
….
RESTORE DATABASE sample
FROM [device_sample]
WITH
FILE = 8,
REPLACE,
STANDBY =’f:\MSSQL\BACKUP\undo_sample.dat’,
MOVE ‘logicalname’ TO ‘f:\MSSQL\physicalname.mdf’,
MOVE ‘logicalname_log’ TO ‘f:\MSSQL\physicalname_log.ldf’
WAITFOR DELAY ’00:00:05′
I tried restoring it using enterprise manager gui and it is writing the .mdf but it stops there it is not writing the .ldf
and the restore progress bar is not moving even though it already wrote the mdf file. I checked many times the file number in the backup device,logical and physical names and it is correct im wondring what is wrong..please help thanks
I have transaction log files which I need to restore onto my db daily to keep the db updated. I will have about 50 log files every day I need to restore at night time. How can I restore using t-sql? Thanks.
HI,
I want to insert logical file names in a table from database backup file (dbbackup.bak). Please suggest me.
You need to restore the database backup file
Hi sir,
I cleared Backup and Restore.But how can i restore the database to running database. I tried to restore this comment, i got this error
“RESTORE cannot process database ‘testDB’ because it is in use by this session. It is recommended that the master database be used when performing this operation.
RESTORE DATABASE is terminating abnormally.”
Pls help me
Thanks
Use master
Go
–your restore command
Hi ,
I’m a bit new to sql.
I have a sql server which has 2 databases. one for training and one for production.
Now, I need to automate the process of refreshing the training database with the production copy.
I understand that I can take the latest full backup of the production database and restore it to Training database.
Is there any thing else that needs to be done?
Can you provide me with the script that does similar kind of refresh activity?
Thanks in advance.
Prakhyath
If you want to do it regularly, have a look at Replication
hello, I’m new to sql server and I need help in restoring the databases.
I have a 90 backup(.bak) databases in disk… how can I restore them at a time…. I can do one at a time… Do I need to write any script or is there any option for that…
Thank you for the help…
You need to loop thru the physical files. Use xp_cmdshell
I want to create password protected database file (i.e. datafile.mdf, datafile.ldf)
So that no one can copy data and attach on other Sql Server.
Please help me….
I think in OS like Windows 7, it is possible
when i am using the above procedure to restore the database i am getting the following error..plz give reply immediately..it is very urgent..it’s showing the error like
The backup set holds a backup of a database other than the existing ‘MyAccountsdDBDec22’ database.
RESTORE DATABASE is terminating abnormally.
You need to use REPLACE option to overwrite the existing logical files
My Dear pinal
I am facing a problem while restoring database in SQL2005
Code
RESTORE DATABASE NWORK2011
FROM DISK = ‘D:DBNWORK2010_backup_201011292100.bak’
WITH MOVE ‘WORKSHOP2004_DATA’TO’D:DATANWORK2011.MDF’,
MOVE ‘WORKSHOP2004_LOG’TO’D:DATANWORK2011.LDF’
GO
Error
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing ‘NWORK2011’ database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
It emans that the database with the same logical files exists already. You need to use REPLACE option at the end of the script
Hi Pinal,
how can we get the logical names of a database from the Backup file.
Restore filelistonly from disk=path of backup file’
hello sir, in my database the size of ldf was higher than the MDf file so i cant able generate the report soon… how reduse
the size of Log file…pls help me
You can take a log backup and then truncate it
Hello Pinal Dave,
I have one problem with backups.
I have taken Full backup,Differentail Backup, Transactional Backup.Now I am trying take Tail log backup by selecting the option “Back up the tail of the log, and leave database in the restoring state”. But i am not able to take this backup ,I am getting the error” (Microsoft.Sqlserver.smoExtended).
Please could you explain what is this error and how to reslove this??
Please mail me to [email removed]
Waiting for your reply.
Thanks A Lot.
Vijay
Thank you very much!
Very helpfull
I have a backup file from SQL Server 2005 and i want to restore it to an existing database, i have NOT selected an option – overwrite the existing database, still data from the backup file is overwriting and my old in the database is lost.
How to avoid above problem, can someone please help me.
It will overwrite even if you dont select that option. That option is there to replace the database with different mdf and ldf names with different locations
Is there any way to backup and restore the jobs in SQL Server 2005, especially without management studio? Or can we create jobs with TSQL via “sqlcmd”? If any, could you give an example, thanks for now..
Hi Pinal,
I’ve been reading your blog re:SQL and I could say I’m learning a lot! Thanks.
But I have a question re: SQL 2005 & SQL 2000. I installed SQL2005 but when I am trying to restore a DB, I am receiving this error: ” Restore failed for Server ‘_’. The media family on device is incorrectly formed. SQL Server cannot process this media family. —> Microsoft.SqlServer.Management.Smo.SmoException: System.Data.SqlClient.SqlError: The media family on device is incorrectly formed. SQL Server cannot process this media family.
Okay, so I checked my version in SQL Management Studio:
select @@version; I found out that it’s MS SQL 2000…
I uninstalled my 2005 and reinstalled again but now I cannot connect to my server…
Please help. Thanks. :)
Lane
it is not possible to restore database of higher version to lower version. Generate script of the database and copy data using SSIS
Hi Pinal,
Can you tell me how to restore system databases in SQL server 2000.
Thanzzz for the sql queries. The queries are really helpful….
when i try to backup or restore a database, the following error was arise.
Msg 3234, Level 16, State 2, Line 1
Logical file ‘Data_file’ is not part of database ‘CourseFinder’. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Please anyone help me.
It means that the logical file path does not exists in the current server. You need to use RESTORE WITH MOVE option to specify the correct path
Hiii Pinal,
My one colleague had restored the database on my db. Unfortunately I didnt take a back up of the same. Can I get the same db before uploading.Please reply me ASAP. I tried with restore point but couldnt help it out. :(