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
I have a table1 in my local database db1, I want to add that table with all its data in at db1 which is stored at my server. What should I do?
Best way is to Generate Create Table Statement using SQL Server Management Studio on your local database.
Run that script on your destination server where you want to create that table.
Once you have the table in new server, There are multiple ways of transferring data between source and destination. You can use Import Export Wizard, DTS, or Linked Servers to transfer data from one db (server) to another db(server).
Easiest would be to use Import Export Wizard.
You can also use Database Publishing Wizard to generate insert statement for your table. This generate a script that you will have to execute on your destination server.
~ IM.
I have the table created in the server db using the script. But I need to find a script that would transfer all the data from the client db to the server db. Import/Export is not a feasible solution as it would violate the PK, FK issues.
Database Publishing wizard works from VS I guess? Not from the SQL Management Studio.
Sir, how can i restore 2008 Db backup to 2005 database ?
No it is not possible. You need to generate script and run in 2005 version. Use SSIS or import/export wizard to copy data
This is very useful blog, I read very often. Thanks for sharing.
Hi Pinal,
I am trying ‘Index Tunning Wizard’ In sql 2000
but its showing me this error msg
‘The Workload does not contain any events or queries that can be tuned against current database. Check to see that trace contains valid events or that SQL Script contains valid queries and you are tuning against the right database.’
Can u help me in this!
Hi Pinal,
my database was corrupted and it showing like DATABASE (SUSPECT)
can you help me how to recover,
thanks
Chandan
Hello Chandan,
May i help you??, what is the version of your SQL server database??
Hi ,
Please suggest me, If i have restored the Database from fullback (.bak) file then it will overwrite the transaction log file (.LDF) file also or not ?? want your prompt response.
Thanks in advance,
Raghu
Hi…. How to taking a Backup of SQL Server 2008 using SQL Query….Kindly help me…
Backup database your_db to disk=’C:db_name.bak’
Hello,
I need to restore AD database to BC database on the same server. Both AD and BC are large databases. I can’t create a bak file as i dont have enough space in the database server. The Restore window in SQL mgmt studio takes lots of time to restore as database is large. Is there any SQL query to restore one database to another on SAME server. Kindly help me
If your goal is to keep them same and if you dont want one of the databases, you can just drop the database BC and rename AD to BC.
sir i want to go database field but i can not know how to start and where..
please tell me any good insititute delhi/ncr who are chief and best for me..
still i am waiting for quick response..
Less experienced people can also use SQL Management Studio to restore database backups. Right-click on your database select Tasks->Restore and follow the instructions.
There are also very simple 3rd. party tools like this to perform simple backup restoration. It can restore full backups from compressed and regular .BAK files.
I want to take backup from MS SQL 2005 to drive (hard disk) using code i.e. when I click backup button through coding backup must be taken to drive(hard disk).
Use this
Backup database db_name to disk=’your path’
Hi
Im Mubeeth from Singapore,im want some help about Replication. Any one can help. if any one have please mail this address
Please tell us what problem you have
Dear sir,
Hello i read your articles and use it its very easy to understand and implementation thanks for providing/sharing such nice articles,
i have one question i just want to take a copy of one database from one pc and want to copy it in another pc,i am using sql server 2000,using script i am able to do it but can you provide the steps to do the script in sql server 2000 with snaps/detail so it will make my work easy.
Thanks in advance
Have a nice day
If they are on the same network, you can restore it usinh UNC Path
In source server run this
Backup database myDB to disk=’D:myDB.bak’
In target server run this
Restore database myDB from disk=’\Source_server_nameDmyDB.bak’
Once again, your blog has given me valuable information! Thanks!
Please let me know if i want to download the backup from server like IP and store into my laptop (i.e server is located in mumbai and Im staying in pune ) own dedicated server how to do it from SQL script or any special software is there …
Regards
Mayur
If there are connected via VPN, you can directly run a restore command. Otherwise copy it to laptop and restore it there seperately
Thanks a lot it worked for me..
If your getting restoration failed while trying to restore the backup.
Error msg: “restore failed for server microsoft.sqlserver.smo” Then use the below solution in MS SQL 2005.
The below is the query i used :
RESTORE FILELISTONLY
FROM DISK = ‘C:DBfilename.bak’
GO
“”once u execute the above query you get the logical filename” use this logical filename in the below query.”
RESTORE DATABASE TestDB
FROM DISK = ‘C:DBfilename.bak’
WITH REPLACE,MOVE ‘Logicalfilename’ TO ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTest_DB.mdf’,
MOVE ‘Logicalfilename_log’ TO ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTest_DB.ldf’,
MOVE ‘sysft_ifindexused_index’ TO ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTest_DB.HRD’
—
Regards
Narashiman K Iyengar
hello,
i need some help here with sql server on win 2008 server running
i have receive a BACKUP.BAK and i would like to restore it.
i did a new DATABASE and i tried to run
RESTORE FILELISTONLY FROM DISK = ‘D:BACKUP.BAK’
Go
came out with this error
Msg 3242, Level 16, State 1, Line 1
The file on device ‘D:BACKUP.BAK’ is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
how do i solve this? thanks
The command should be
RESTORE FILELISTONLY FROM DISK = ‘D:BACKUP.BAK’
Hi Pinal Dave,
I have some amateur sort of questions. Kindly clear them. Thanks
Refering to the original script:
=========================
RESTORE DATABASE YourDB
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.ldf’
1. If i had multiple log files when i took the backup then on restore operation, will multiple files be created or a single ldf and single mdf file will be created in Database? (i presume 01 mdf and 01 ldf for the said database be created.)
2. When we create a backup of a database that is spanned on multiple mdf/ndf and ldf files, then is the structure of the resulting backup (.bak) file same as source files (does .bak files has single data and signle log file?)?
Reply soon
Dear Mayur.
Ask a question Comprehensibly. Exatly what do u want.
Your posts are really helpful.
This is my implementation of the commands
RESTORE DATABASE MyFiles
FROM DISK = ‘E:\My Documents\SQL\storeMyFiles-Database\StoreMyFiles.bak’
WITH MOVE ‘MyFiles’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\myFiles.mdf’,
MOVE ‘MyFiles_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\myFiles_log.ldf’
and this is the result
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 have seen that you need 2 files which I don’t have even though the backup file seem to have the .mdf and .ldf files, however I have not seen the problem mentioned here.
Maybe you can help. Thanks in advance
Are you trying to restore the database with higher version to the lower version of SQL Server?