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
Pritesh:
I’m not an expert but:
is your LDF line correct?
MOVE ‘E:TCgohil.ldf’ TO ‘C:Program FilesMicrosoft SQL ServerMSSQLgohil.mdf’
Should it be gohil.ldf ?
I use database labels already in the database to restore
(Backup is a full backup)
Use GOHIL
Restore database GOHIL
from disk = ‘D:GOHIL_db_200908240200.BAK’
with move ‘GOHIL_DATA’ TO ‘C:Program FilesMicrosoft SQL ServerMSSQLDatagohil.mdf’,
Move ‘GOHIL_LOG’ TO ‘C:Program FilesMicrosoft SQL ServerMSSQLDatagohil.ldf’,
stats = 5
go
Rick
Thanks Pinal for your help.
It really helped me a lot
how to take backup of database using query .
pls help me out.
????????????????
Hi,
Any body know’s my doubt just publish your idea!…
I want to take a backup for one table in script format..
like below this,
Insert into tblStudent values (1,’Raja’)
Insert into tblStudent values (2,’Sindhu’)
This is very urgent know any one please help me,
Thanks,
Maniraj…
Hi,
Take a backup for database using query….
Backup database dbName to disk=’E:\NewFolder’
Then,
Restore database dbName from disk=’E\NewFolder’
That’s it…
Maniraj….
@Maniraj
Untill SQL Server 2005, using SQL Server native tools, you cannot take backup of a table in a database. You can only take backup of database or filegroup(s) in a database.
You said, you want to take backup in a script format, that is possible using SQL Server Tool by name : Database Publishing Wizard.
For a step by step pictorial explanation of how you can generate script for a table with data, check this link:
http://products.secureserver.net/products/hosting/PublishingWithDPW.htm
~ IM.
hi pinal,
can u pls explain me. how to restore my .bak file with sql server 2005 wizard. i think it is very easy to understand then query.
i m trying this,
i m here in sql server 2005 wizard
1. ASHUTOSH/DATABASE/
2. then right click on screen it will dispaly menu which have one option “Restore Database” then……????
hi
There is a problem with my project. i develope a windows based application in c# with sql server 2005 and develop a web site in Asp.net with sql server 2005 with same database .
Now i want to update my windows application database with one single click at client side . and wana take backup and restore the backup . Please tell me the Proper process to do so . And if i have the few extra tables at web database , there can be any problem to update it or not, if yes suggest me the solution
Hi Pnal,
can we take backup on networkpath
like ‘\\servername\sharefoldername\backupfilename.sqlbackup of .bkp’
i used this query
‘BACKUP DATABASE MYDATABASE TO DISK =\\servername\sharefoldername\backupfilename.bkp’ WITH INIT
but this is not working.
Error:-Cannot open backup device ‘\\servername\sharefoldername\backupfilename.bkp’. Device error or device off-line. See the SQL Server error log for more details.
please tell me the query and can we use this query in dotnet
Thnaks in Advance
Regards
Raj
Hi,
Quick post just to point out a mispelling in the script : you requiered to rename the .LDF file to .MDF. Seems it should be .LDF :)
Great job ;)
Hello Pinal,
I have a backup from a database with a single data file in the primary filegroup and would like to restore it to a database with multiple files in the primary filegroup. However, the restore always reverts the configuration for the new database back to a single file. What is the trick to make this work?
Example:
CREATE DATABASE [Test] ON PRIMARY
( NAME = N’Test_data1′, FILENAME = N’D:\Test_data1.mdf’ , SIZE = 2048KB , FILEGROWTH = 10%),
( NAME = N’Test_data2′, FILENAME = N’D:\Test_data2.ndf’ , SIZE = 2048KB , FILEGROWTH = 10%),
LOG ON
( NAME = N’Test_log’, FILENAME = N’D:\Test_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
GO
RESTORE DATABASE [Test]
FROM DISK = N’D:\Test.bak’ WITH RECOVERY, FILE = 1,
MOVE N’Test_data’ TO N’D:\Test_data1.mdf’,
MOVE N’Test_dog’ TO N’D:\Test_log.ldf’
, NOUNLOAD, REPLACE, STATS = 10
GO
The restore ends up deleting the Test_data2.ndf file and I am left with a database with only one data file.
Thank you, Bradley
Hello Mr.Pinal,
I have a requirement where in i have to get the database backup and restore when required.
I have to do this in a web application. The application has been developed using Java/J2EE technologies.
I have tried database backup and restore using JDBC…..but it didnot worked ….
Need help regarding…..
Iam using SQL Server 2005 database….
Can you please mention the steps for performing the above nmentioned tasks
thanks
Hi,
I am here to ask you help regarding Dotnetnuke with SQL Server 2008 express managment studio.
I have dotnetnuke website backup and Database backup of 2005 sql server .bak and I uninstalled the 2005 and installed 2008 sql server express managment studio.
In iis7 i pointed the new website to backup of Dotnetnuke site, and restored the .bak (2005) on 2008 sql server express managment studio. It not worked for me.
earlyer what i did the same when i done on 2005 it was done. not on 2008.
can you plz help..
Hi, i want to know how to restore database(.mdf) in sql server 2005.
Dear Sirs
I have a backup from SQL Server 2005 and want to restore it on SQL Server 2000. Is this possible.
Regards
abrar
This issue was resolved my using wizard functionality of moving sql2000 to sql 2005
Hi, i was executing a restore process on sql 2005 but i had toi cancel it couse the users told me that the system was too slow, i didnt know that the reason was that some user was running a big process well, now the data base appears like it is restoring and i can stop that process, how do i do it?
Simple restore commad
I need a simple restore command with current date.
Restore database with today backup file only. can any body helps me in this regards.
remo
How could I restore a SQL 2005 database which has full-text catalog to a SQL 2008 database with T-SQL statement?
I can restore the database with SSMS GUI without any problem.
Thanks in advance.
Very useful article. Thanks a lot.
I wrote a simple T-SQL to take backup and then restore it.
I used “WITH PASSWORD” Option to protect my database backup from unauthorized access.
BACKUP DATABASE PracticeDB
TO DISK=’C:PracticeD.bak’
WITH DESCRIPTION=’Database Backup’,
INIT,
PASSWORD=’TryAgain’
But anyone can overwrite it by taking the backup again. So How can I fully protect my database backup.
(Please have a look at what I have done h ttp://www.techpint.com/programming/sql-server-backup-and-restore-database-using-t-sql)
Thanks in advance.
I finally got my restore to work between 2 servers. The script below concatenates the name of the file to match the name of my nightly backup which includes the date. The only time a problem would be Jan 1 when the year is off, but if I work on it some more, I think I can work that out. Make a job on the server that needs to get the backup restored onto it and then make this a step in the job and schedule it as you need it to run.
Declare @vBackupPath varchar(46)
set @vBackupPath =
‘\\NameofServerContainingBackup\NameofFolderContainingBackup\FirstPartBackupFileName’ +
cast(Year(getdate()) as char(4)) +
case when Month(getdate()) between 1 and 9
then ‘0’ + rtrim(cast(Month(getdate()) as char(2)))
else rtrim(cast(Month(getdate()) as char(2)))end +
case when Day(getdate()) between 1 and 9
then ‘0’ + rtrim(cast(Day(getdate()) as char(2)))
else rtrim(cast(Day(getdate()) as char(2)))end
+
‘RestOfYourBackupFileName.BAK’
RESTORE DATABASE NameOfDBYouWantToRestore
FROM DISK = @vBackupPath
WITH REPLACE,
MOVE ‘YourFile.mdf’ TO ‘E:\MSSQL\Data\YourDBName.mdf’,
MOVE ‘YourLog.ldf’ TO ‘E:\MSSQL\Data\YourLog.ldf’