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,
1. i have an sql 2000 full database backup, say mybackup1.bak. when i restore it to another sql 2000 instance, it failed. After investigating, i found in the ‘view contents’ options it says ***INCOMPLETE***. I myself did the backup of database from the customer pc. and copied to it to anothe system.
2. i tried to restore mybackup1.bak with ms sql 2008 express edition. it says ‘specified cast of not valid. SqlManagerUI’. also the contents option of the restore says ‘Object cannot be cast from dbnull to other types. (mscorlib).
could you please advise on this?
Thanks,
Ann Mary
i copied the backup file from one system(sql server 2008R2) to another system (sql server 2008R2) can i restore this backup file without any errors? if both are in same domain what is the solution or not in the same domain what will be the solution?
You can restore it. Have you tried it?
Hi Pinal Dave i am expecting answer please…
hi plz tel me sql server backup and restore steps…
am waiting 4 ur rply
It works perfect when I run in SQL Server Management studio. however When I sent this sql command with Php the recovering database stuckin-restoring. Any Idea about this problem ?
your help would be highly appreciated
Thanks you.
Hi Pinal Dave,
I am creating one application for restoring the database. A user, select the .bak file through this application and need to restore the database (Which is reside in server). So, I pass the selected file as a binary data to the database. Is it possible to restore the database from a binary data?
Thanks in advance.
Dear Pinal Dave,
I would be better if you could provide a solution/anwer that I am looking for.
Regards,
Ann Mary
Dear Pinal Dave,
I want to restore multiple database from single .bak file.
How can i do this??? plz sugget me ……
I have tried following steps from your blog but getting errors…
RESTORE FILELISTONLY
FROM DISK = ‘E:\MCSDB_NEW_BACKUP.bak’
GO
—-Make Database to single user Mode
ALTER DATABASE TEST
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
—-Restore Database
RESTORE DATABASE TEST
FROM DISK = ‘E:\MCSDB_NEW_BACKUP.bak’
WITH MOVE ‘TEST’ TO ‘F:TEST.mdf’,
MOVE ‘TEST’ TO ‘F:TEST_log.ldf’
ALTER DATABASE TEST SET MULTI_USER
GO
Error-
Msg 3154, Level 16, State 4, Line 8
The backup set holds a backup of a database other than the existing ‘TEST’ database.
Msg 3013, Level 16, State 1, Line 8
RESTORE DATABASE is terminating abnormally.
Hi Pin,
Getting an error i.e ‘The backup set holds a backup of a database other than the existing ‘OP1_2c659041_0de9_4bfc_93e8_fa394b503424’ database.
‘ while using the “Step 2: Use the values in the LogicalName Column in following Step.”
Please help me out…
It means you are trying to restore the db backedup from higher version to lower version
Sir,
I am restoring Database using TSQL as per given step
but when i fired step 2 then one error has occured
“Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database ‘MDM’ because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.”
so i fired next step: SET MULTI_USER
GO
then it display 1 msg
Command(s) completed successfully.
after this step tables of Database is not displaying
kindly guide me………..
Thanx In Advance
As it says you are connected to the database MDM. So switch to master database
USE Master
GO
your restore script here
How to restore database from sql script?
Restore database db_name from disk=’file path’
Hi Pinal Dave,
I have a question regarding the database restore.
If we want to update / synch the data with the latest data on pre latest database, Can we restore the database by using the latest bak file.
In this case , should we re create the users and provide them the accesss to db objects…?
Please let me know best steps.
Restoring database will not affect users and their permissions
how to restore databases of express edition to standard sql server edition
DECLARE
@BackupFile varchar(8000),
@sql varchar(8000)
SET @BackupFile = ‘D:adventureworks2008r2.bak’
SET @sql = ‘RESTORE DATABASE adventureworks2008r2 FROM DISK = ”’ + @backupfile + ”’ WITH FILE = 1,
MOVE N”adventureworks2008r2_data” TO N”D:adventureworks2008r2.mdf”,
MOVE N”adventureworks2008r2_log” TO N”D:adventureworks2008r2.ldf”,
NOUNLOAD, REPLACE, STATS = 10, RECOVERY’
exec (@sql)
go
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 23088 pages for database ‘adventureworks2008r2’, file ‘AdventureWorks2008R2_Data’ on file 1.
Processed 3 pages for database ‘adventureworks2008r2’, file ‘AdventureWorks2008R2_Log’ on file 1.
RESTORE DATABASE successfully processed 23091 pages in 9.307 seconds (19.382 MB/sec).
–simple method
RESTORE DATABASE adventureworks2008r2 FROM DISK = ‘D:adventureworks2008r2.bak’ WITH FILE = 1,
MOVE ‘adventureworks2008r2_data’ TO ‘D:adventureworks2008r2.mdf’,
MOVE ‘adventureworks2008r2_log’ TO ‘D:adventureworks2008r2.ldf’
Hello,
Back Up and Restore with merge and duplication check
I want to backup of my database and at time of restore i dont want to loss my old data i want to update database.How it is possible…?
Currently in my application i take backup in XML file and while restore i loop through cursor…and Read XML file data one by one table’s Row…
It takes too much time…..
give me better solution… pls….
excelen post..
Tank you!!!!!!!
Hi Pinal,
I want to export or take backup of a table rows older than 30 days from the current time before deleting the same using a job scheduler but I’m unable to do the same using a query.
Could you please suggest me the query to export or take backup of a table rows using the below condition.
SELECT * FROM test.[dbo].[TBL_Datahistory] where SYSTEMDATE<=GETDATE() – 30
Thanks in advance.
Regards,
Ravindra Gohil
declare @d char(8), @sql varchar(1000)
set @d=convert(char(8),getdate(),112)
set @sql=’SELECT * into new_table_’+@d+’ FROM test.[dbo].[TBL_Datahistory] where SYSTEMDATE<=GETDATE() – 30 '
exec(@sql)
Thanks Madhivanan for the reply, but i want to export the selected rows into a .csv file instead of putting them into a new table.
Use bcp
i want to take the backup automatically in sql server 2005 at regular interval…..plz suggest me…how to do this task……