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 Pinal, i need a help urgently
i have a backupfile located on \\kftusoktulsps14\public\Infosys\EU DB backup\Prod_Teamwork_EU10_BAK.bak
i have to restore it on different server which is KFTUSOKTULSPS78
with the name of Prod_Teamwork_EU10_04282010
please create a script for me and help me as soon as you can,
Thanks in advance
Restore database Prod_Teamwork_EU10_04282010
from disk=’\kftusoktulsps14publicInfosysEU DB backupProd_Teamwork_EU10_BAK.bak
‘
Hi
I have a sql script to restore my database :
RESTORE DATABASE [PIScRestore001]
FROM DISK = N’\\FSERVER\Development\Applications\PIDevelopment\StartupApp\Payroll\bin\Debug\MasterDB\BlankDB.bak’
WITH FILE = 1,
Move N’Payroll_Data’ TO N’F:\Databases\PIScRestore001.MDF’,
Move N’Payroll_Log’ TO N’F:\Databases\PIScRestore001.LDF’,
NOUNLOAD, STATS = 10
AND i get this error:
Msg 3201, Level 16, State 2, Line 2
Cannot open backup device ‘\\FSERVER\Development\Applications\PIDevelopment\StartupApp\Payroll\bin\Debug\MasterDB\BlankDB.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
it is some permissions related issue
but its very difficult to give permissions to all systems on a network
does anybody have a proper solution for this issue where the location of a backup is retrieved at runtime at remote m/c and i have to use that database?
There is no way other than giving the proper permission to access the file
Hi Pinal Dave,
I hope you can help me, I’m not a technical person by profession, I have a problem that my web hosting service wont help with. I hope you can :-)
Canyou tell me how to identify the ‘create database’ line in a V4 .sql backup so that I can restore the back-up to a V5 database for my wordpress blog?
Background.
To upgrade my wordpress blog to 2.9.2 I needed to upgrade my sql server from V4 to V5
I used the wordpress guidance to create a backup .sql file of my V4 database.
Using my web hosting service UI (GoDaddy) I created a V5 sql database, upgraded my wordpress, posted a few posts on the blog to check it all works – it does :-).
Then I tried to restore the V4 backup first using GoDaddy UI – it gave me a success message but didnt actually restore, then using MyPhpAdmin (StarfieldTech), again it gave me a success message without actually restoring the backup. Both support services told me the failure is because I still have the you “create database line in your SQL file”. They will not tell me how to identify this line.
Searching the backup SQL file (in wordpad) for ‘Create Database’ produces no results. I do not know what line to delete in order to restore my database. Please help me :-)
best wishes, Wendy (UK),
Sir ji
Gud Afternoon
i have read your many articles,these articales are very usefill and i have learned .
i have a problem sir
how can we read .ldf file of a database
thank u sir
You cant directly read. You need to make use of some third party log readers
Hi,
I am trying to import a database over the network. While doing so I am getting following error .
Please let me know I any body have the solution for the same.
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device ‘\\10.15.21.29\g\Intel\Mylearning4Saba.BAK’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Thanks & Regards,
Yogesh
Is it a shared file?
Try
‘\10.15.21.29g$IntelMylearning4Saba.BAK’.
Tried it already
Make sure the server has enough permission to access that file
can any body plase explain me
How Backup Works in sql server 2005
1.create database ABC
2.backup database ABC to disk=’D:\backup\DB\ABC.bak’
Here i created a new database ‘ABC’.NOW my DATABASE is empty(no objects where created), i am taking full back up,the out put i am getting
OUTPUT:
Processed 152 pages for database ‘ABC’, file ‘ABC’ on file 1.
Processed 2 pages for database ‘ABC’, file ‘ABC_log’ on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.267 seconds (4.698 MB/sec).
MY dout is even though my database is not having any objects,why it is backup 152 pages data file,
i did’t done any transations even why it backuped 2 pages of log files.
2) AGAIN when i take the LOG BACKUP ,with out doing any transaction again it showing that ” 2 log pages where backuped”
backup log ABC to disk=’D:\backup\DB\ABC.bak’
OUTPUT:
Processed 2 pages for database ‘ABC’, file ‘ABC_log’ on file 2.
BACKUP LOG successfully processed 2 pages in 0.056 seconds (0.219 MB/sec).
3)AGAIN when i take the log backup ,it showing ‘ZERO pages backuped'(no transation were done)
backup log ABC to disk=’D:\backup\DB\ABC.bak’
OUTPUT:
Processed 0 pages for database ‘ABC’, file ‘ABC_log’ on file 3.
BACKUP LOG successfully processed 0 pages in 0.033 seconds (0.000 MB/sec).
hi…
i m nt restore the db 2000 on the srver godday properly and nt get the value get the from db if u can help me than soooooooooo thank .
Can you give some claer informations?
It is difficult to understand what you want
Hi Pinal,
I have got a problem, I was working on database and while testing a Stored Procedure, unfortunately a querry of delete got executed and my data is deleted. But don’t know how to recover back that data.
And i haven’t use transaction block so, not able to rollback that transaction. please help me out to get back my data.
Thanks,
Vineet
If you have latest backup, you can try restoring it
Hi pinal,
got stuck in database, where unfortunaltely got my data deleted and not even used transaction block. As data is deleted while execution of a Stored Procedure. So please help me out to retreive data back and FYI my database is on SQL Server 2008.
Please help me out as soon as possible.
thanks,
Vineet
It is not easy to get data back until you try some third party tools that read data from log files
Hey Can U help me how to take my database back up .
the database is in my online server and I am using MS SQL Server.
Plz help me soon….
I am waiting for ur reply.
Backup database your_db to disk=’file_path’
Hi Pinal,
I am Senthil. I have one doubt in Export and Import in sql server. Is it possible schema level export and Import in Sql server? , just like Oracle Sql * Loader.
is there Any utility available in sql server?
Thanks
T.Senthil
Can you explain what is schema level export?
Hi Pinal,
Really very good article!
I have database called “xyz” which is already setup over client’s machine now I need to add new columns to that database in my new setup file and when I will run that setup on existing database make sure that existing data of “xyz” should not get lost. New columns should be added without affecting current data.
How can I achieve this using T-SQL statements?
Thanks,
Harry
Script the alter table statements for the newly added columns and run them at the client’s database
by mistake i have delete delete database from sql server and i have no backup plz tell me how i restore or recover db (mdf, ldf) files.
how can you enter your home if you have lost your keys….as simple as that….restoration is seconds step after backup.
you missing your first step so cant go further.
Try to find you must be having either backup or mdf or mdf and ldf file. without this you cant recover.
Hello,
Can I use the “restore backup” process to create a new database? In other words, I need to take the backup from my shared host and use it to create the same database on our new dedicated server.
Thanks,
Doug
Yes. It is possible. Read about Restore command in SQL Server help file
yes, can do that by using restore database and provide the database name and then logical name of data file and log file and physical location of these files with physical name.
Can anybody please help me to restore a database which had peer to peer transactional replication applied to it…..i restored the database n used KEEP_REPLICATION to keep the replication but it didn’t worked.
any comments please.
Thanx
hi ,
I have .bak file i want to import it into mysql .how?
You can’t directly import to mysql. Restore it in SQL Server and use import/export wizard from there
sir,
i using sql server 2005 & management studio in windows 7. i take backup sql database successfully. but i use another system sql server 2005 & management studio in windows xp in a lan connection. in this xp i cannot restore.
how to transfer the windows 7 database in xp..
please help me..
its very urgent