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 full backup a 00 hour midnight and every hours Transaction log backup.
If my database crashed at 10:55 AM. then how can I restore 55 minutes data as I have last log backup available at 10:00 AM
Hi ,
I am getting following error while restoring database
“The media set has 2 media families but only 1 are provided. All members must be provided.”
What is solution of it . How to restore it without taking another backup
Are you restoring backup file to the lower version of SQL Server?
Hai Pinal
we are having Servers (SQl server 2005) in Ilaly and India, we need to Make Logshipping between these two Servers.
Logshipping should be happen from Iltaly server to Indian server, so i configured the set up in Ilaly Server, here my problem is i am not able to connect with indian server from Italy server.
i selected the TCP/IP Option in the “Sql Server Surface Area configuration” and tried with using IP address, But No use
In the Secondary Server Settings Panel when i click the Connect button it shows only the Local and Network Servers of Italy Server, How can i connect with our Indian Server?
Please suggest me what i have to do?
is there any othere configuration Needed by System Admin or anything else ?
Please give me some idea and help me
Thanks in Advance
Senthil Kumar T
Thankyou very much it helped me a lot
Hi Dev,
Will you please give an example how can i create a sql-server 2008 job to restore the latest database.
Thanks,
Rajnish
Refer this post and apply the logic used there
Hi,
I have 400 sql Db’s backup file (*.bak). i need to restore all the 400 DB’s in one single shot. how can i do it?
You need to loop thru all the files and do restore
Read about xp_cmdshell to know about reading file names from the file system directory
Hi,
how to backup a database from local system to public ip?
Please help me ASAP
Thanks in advance.
Use UNC Path like ‘\sysnamefilename
Hi,
I created a dummy database and then right cilck on dummy – Taska – Restore – Database. In source of restore I selected from database and from drop down list I selected Main database.
But after doing this, data was from my Main database was lost. Can anyone help me understanding how it happened and how can I (if possible) restore the lost data.
Thanks
HI There
How do you automate backup and recovery using a stored procedure?
i did not know where to put my Question so i have asked here
how to change the Administrator username/pass for sql 2008 ?
Hi Pinal,
I have been facing an issue. We have developed an application(DOTNET 2.0) which needs SQL jobs. Unfortunately, the server on which we hosted the application has SQL server 2005 express edition. So I am developing a custom EXE which takes the automatic backup of the database at a particular time daily when scheduler using windows scheduler. I am supposed to write a SQL script which takes the automatic backup of database. I will call this script in stored procedure in the code. Can this be achieved? I got an article in your blog which restores the database. I could not find articles which help in doing taking database backup. Please let me know whether this can be achieved. If yes, kindly suggest some ways to do it.
Regards
Shreesh Onkar
Hi,
I have to restore DB from particular file path(C:\temp) without giving databse name (ex: sampleDB.bak). Coz our back DB name has been changing daily. So can i write the SQL scripting to run the auto restore it?
Thanks for your kindful rapid reply …
What is the format of the file? Is date value appended?
Hi
I have a database in replication from some time… everything is working fine, but now is the time to change the distributor server (hardware). I’ll backup and restore the database on the new server, but I wonder whether is there a possibility to see what tables are published for replication, because I forgot, or even to ‘backup and restore’ the replication process.
Thank you for any answer.
I got the following error message when i am trying to restore the backup to sqlserver2005(Express)
TITLE: Microsoft SQL Server Management Studio Express
——————————
Restore failed for Server ‘THAMESSQLEXPRESS’. (Microsoft.SqlServer.Express.Smo)
For help, click:
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The operating system returned the error ‘5(Access is denied.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLAhaliya.mdf’. (Microsoft.SqlServer.Express.Smo)
For help, click:
——————————
BUTTONS:
OK
——————————
HI pinal,
I m trying to back up database (on server) over lan.
if i m on my local pc and want to take back up of a database (on server) on my local pc, is it possible?
i m trying this code,
BACKUP DATABASE TO DISK = ‘\\node\sharedfoldername\bkupname.bak’
but it gives me error,
“Device error or device off-line. See the SQL Server error log for more details.”
Can u help me in this!!!!!
hiii,
i forgot to the save the back up file 30-09-2010 but its automaticaly remoed can i recover tat pls tel me soon
hiii,
i forgot to the save the back up file 30-09-2010 but its automaticaly removed can i recover tat pls tel me soon
pls give me a solution
Hi Pinel,
I am getting strange error while doing backup on SQL2005 server —
TITLE: Microsoft SQL Server Management Studio
——————————
Backup failed for Server ”. (Microsoft.SqlServer.Smo)
For help, click:
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The media family on device ‘.BAK’ is incorrectly formed. SQL Server cannot process this media family. (Microsoft.SqlServer.Smo)
For help, click:
——————————
BUTTONS:
OK
——————————
Any comment on it.
Note: I am not restoring DB…..
Hope you can help?
I am using MOOS 2007 with SQL 2008 and trying to make a backup/restore with SQL the content DB.
The scenaria is like this:
The Organization I work for is small and therefore we have decided to keep both the MOSS and SQL in one Server. Now,
The Server1 hold as mentioned the application and content_DB that I need to play around with. Server2 has identical installation and serves as backup with alternate url’s for web-applications.
My question:
How to make the backup of content_DB from Server1 and restore the same in Server2 while both server have the Content_DB in use, If there is any way to achieve this?
Thank you
I am restoring a SQL db to a terminal server: I am stuck on an error:
RESTORE DATABASE afwdv1Data FROM DISK=’F:\Microsoft SQL Server\AGENASQL\(local)\AFW1\DATABASE_AFW1.bak’
WITH MOVE ‘afwdv1Data’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\afwdv1Data.mdf’,
RESTORE DATABASE afwdv1aData FROM DISK=’F:\Microsoft SQL Server\AGENASQL\(local)\AFW1\DATABASE_AFW1.bak’
WITH MOVE ‘afwdv1aData’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\afwdv1Data.mdf’,
MOVE ‘afwlog1’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\afwlog1.ldf’
ERROR: Incorrect syntax near the keyword ‘RESTORE’. I have an .mdf .ndf and .ldf. Not much experience in SQL so any help is appreciated.
Thanks!!
Why are you using two restore statements? The second one seems to be correct