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
@mahesh
RESTORE DATABASE ABC FROM DISK = ‘\\servername\c$\XYZ.bak’
@indy.
Pls use database maintaince plan and schedule them accordingly.
Hello every body
I have problem in SQL 7.0 database
the problem is that database size is not increasing after 4 to 5 months though the work is going on.
thanks
arjouna
Hi all,
I deleted 4 rows of data from a table in the data base(sql server 2005),i dont have backup of database,this i did one week back now i want to restore that 4 rows to the same table,i used only delete statement in query analyzer ,is it possible to restore that data,if so plz suggest me how can i achieve that,its very urgent the loss this data made my front end application not to work
Thanks in Advance………
Suresh
Why some day one stored procedure take less time and another day same procedure take more time?
Will you please help me with detailed answer?
Thanks
Shailesh
Search about Parameter sniffing in Google/Bing
Hi Dave!
You are right that i can Restore my DB by:
RESTORE DATABASE YourDB
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.mdf’
But what if i have multi MDF and Multi LDF filesgroups…say, i have 200 files all togather
Now how many time i will type WITH MOVE…WITH MOVE,
Is there any easy solution to that?
Thank you for your help.
Rana.
Hi guys,
i would like to know is there any features in sql server 2005 management studio that can backup tables and data in script format?
the purpose is to restore at cilent(customer)’s site in script format instead of database it self.
really appreciate if you could help me immediately. its very urgent.
thank you
~akram~
hi all,
i have mistakenly delete my database .ndf file and now the database is not working is i have a database backup and when i restore the databse still my database is not working any idea how to restore my database
onw thing i want to tell you ppls is that .ndf file is located in E drive and E drive is removed and i copyed the .ndf file from E to F drive.
help me how to slove this prb.
Hi Dave,
I have a question i.e. What is the difference between SQL and T-SQL??
Please help me
Thanks & Regards,
Lipika
Hi,
i want to copy a dataBase from Another Database on the same server with T-SQL instructions without Using DTS
how can i do this operation ?
I have a Prod DataBase and a SandBox DataBase
i want from time to time Copy the Prod DataBase into the SandBox DataBase.
All this with T-SQL Instruction.
thx in advance,
regards,
@Paul.
The best way to copy the database on the same server, I would say,
Take backup and make a new copy by restoring the same backup, this is easy, fast and hassle free.
to do this perform the following actions,
1. Take backup of the database you want to make copy of.
2. Use the same backup, and restore the backup with a new name, you want to change the location of the files, using with move option, but this should be easy.
Very simple.
Thanks,
Imran.
Please tell me who give me training of sql 2005 DBA. in pune.
Hi Pinal, just a quick question, i have restored a full database backup and now i have to restore 32 individual transaction log files to bring it to the state of completion. Is there a way to write a script that will prevent me from writing individual 32 restore t-log scripts for each transaction?
thanks
Could you please let me know if any sites / links to help me in Practicing Backs and Restote Strategies. I need to learn and Practice the different types of Backups.
@Sai,
This is the in detail information for Backups I saw on internet, This explains with screen shots and detailed explanation,
Go to Google.com type : SQL Server 2000 Backup and Restore
click the first link, that will give you detail information about backups in SQL Server 2000, 2005 is almost same for backups.
Scroll down on web page to see screen shots,
Hope this helps,
Imran.
Hi
I am having a problem with restoring a database.
we are planing for a reporting server.i have to restore the data in to that server. back up size is 79 gb. I have two disks of 40gb each.can any one help me in restoring the database . to two different disk.40Gb data on one disk and remaining 39Gb on other.
Thanks In adnavce
Raghu
hi! my name is happy ,
is there anyone can help me..,
i would like to ask how to LAN vb 6.0 programs with the SQL SERVER 2000.
– i developed a program that SQL SERVER 2000 as the backend and i dont know how the client can connect to server.
– how to mount the .mdf
– what connection string do i use? ( code )
– can you please give me a step by step process how to do that..,
thank you.., any response is well appriciated..
Dear,
Can I Get .mdf file location using T-SQL Statement??
Thanx
With Regards,
Shailesh Khanesha
(NIC)
EXEC sp_helpdb 'db_name'
Hi All,
This is Chitra.
Please guide me as to how to take backup of a table in another table in SQL Server for the last 3 days.
Please provide me the script.
Regards,
Chitra
hi
SQLServer2005 backupdatabase in need to move that database to SQLServer2000.
it giving me …compatability error
is there any work around