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 recently downloaded a file from murach.com (Murach’s ADO.NET 3.5, LINQ, and the Entity Framework with C# 2008) which has got all the codes to work, which are present in the book.
the database file given is developed using sql server2005 and i’m using sql2000 server. can I make use of that datbase in any way.
please reply.
thanks
Hi,
I have separate backup file with Datafile and Logfile from sql server 2000. I want restore into sql server 2005.How can i restore it.please any one help Me.
Thanks
T.Senthil kumar
Hello Senthi,
I think you have copy of .mdf and .ldf files. You can use sp_attache_db stored procedure to attach the database.
Regards,
Pinal Dave
Hello pinal dave,
This command is working fine…
Thanks for your help…
Thanks
T.Senthil kumar
If this a typo
RESTORE DATABASE YourDB
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.mdf’
should it not read LDF and not .mdf
MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.mdf’
Hi Pinal,
I have to restore SQL Server 2000 Files (Cognos Samples) into SLQ 2008, your help is greatly appriciated.
Thanks in advance,
Sunil.
I am able to import the samples in 2008. Thanks.
Hi Sir,
Is there any way to publication in sqlexpress 2005 with sqlserver 2000.
Actually i have a offline database(localhost) and another same online database(eg. http://www.abc.com), Sir i want to use replication between sqlexpress and sqlserver2000. is this possible ?.
I want it urgently.
Thanks in advance.
Warm Regards
Raj Thapliyal
Hello Raj,
You can set replication between SQL Server 2000 and 2005 but SQL Server 2005 express edition can be a subscriber, not a publisher.
Regards,
Pinal Dave
How can I restore backup of sql server2000 in sql server2005?
Hi Samidh,
You use this command.
RESTORE DATABASE DatabaseName
FROM DISK = N’E:\Transfer_06Apr2010.bak’
WITH FILE = 1,
MOVE N’Transfer_Data(Datafile of Old Database)’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Transfer2.MDF(Datafile of Restore Database(new Database))’,
MOVE N’Transfer_Log(Logfile of Old Database)’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Transfer2_log.LDF(Logfile of Restore Database(new Database))’,
NOUNLOAD, STATS = 10
Thanks & Regards
T.Senthilkumar
Hello,
I have one situation ,letsay one data base is completely deleted, but i have a saved full backup,Now is it possible to Restore the data base,
While i am trying to restore I have followed the below steps
1)created a new database with Old database name
2)tasks–>Restore–>Database–>select from file–> selected the old data base backup file *.bak>then click on Ok.
The i am receivig follwing the error
********************************************
TITLE: Microsoft SQL Server Management Studio
——————————
Restore failed for Server ”. (Microsoft.SqlServer.Smo)
For help, click:
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘DBtest’ database. (Microsoft.SqlServer.Smo)
For help, click:
********************************************
Please let me know any other tasks that i need to perform before restoring,,
Thanks
Saty
So I followed the steps listed for restoring my db from the .bak file. Here is my script
RESTORE FILELISTONLY
FROM DISK=’E:ColorsQCMacys2.bak’
—————————————————–
then, using the logical names, and the MOVE function
—————————————————–
ALTER DATABASE ColorQCMacys
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
—–restore db
RESTORE DATABASE ColorsQCMacys
FROM DISK = ‘E:ColorsQCMacys2.bak’
WITH MOVE ‘ColorsQC’ TO ‘my location.mdf’,
MOVE ‘ColorsQC_log’ TO ‘my location.ldf’
and I received the following error:
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database ‘ColorsQCMacys’, or the database does not exist.
MSG 5069, Level 16, State 1, Line 1
ALTER DATABASEstatement failed.
processed 12592 pages for database ‘ColorsQCMacys’, file ‘ColorsQC’ on file 1.
Processed 3 pages for database ‘ColorsQCMacys’, file ‘ColorsQC_log’ on file 1.
RESTORE DATABASE successfully processed 12595 pages in 1.459 seconds (70.717 MB/sec).
So, does/will that have any negative effect on my db? It appears to be there, I’d just like to know if those cryptic error messages mean that something is not going to work properly down the road. Thanks so much for posting up relevant articles! You have no idea…. ;)
Hi Sir!
My self Manoj Singh actually i have join a company where used sql 7 now i want to proceed with sql 2005 .
So plz tell me how i convert ..it. without Interrupt my all running application in in organization.
You can take a backup in version 7 and restore it in version 2005. Change the connection string and the application will run
Also make sure to read about behavioural changes in migrating from one version to another version
Exactly what I was looking for.
Do you sell SQL Authority T-shirts?
Hi Pinal,
I am regular visitor of this website and its very helpful for me.
I have a question for you.
I want so see the backup process in sql server 2000 is it possible.
or alse is there any command for backup which will show the percentage complete of backup
Thanks,
Rajul
I dont think there is a way to do this
May be third party can support
But how does it matter?
I have updated table with update query, now i want to restore previous data.
If you used transaction, you can rollback it
Otherwsie restore data from the latest backup file
Hi,
I got a transactional replication scenario; and I wants to know if my published DB fails:
1. Could I recover the DB using the Subscriber Db
2. If possible how to recover it?
Retrive the Logical file name of the database from backup.
Restore filelistonly from disk=’back_up_file_path’
I need to know how to get backUP in sql.
i am fresher.i am working SAP. i need to learn SQL deeply.Can you Give me a PDF which is easy to learn?
is it the right way to Do backup in SQL ?
use LRPTesting;
Go
backup database LRPTesting
TO DISK = ‘D:\VGN1.BAK’
WITH FORMAT,
MEDIANAME = ‘Z_SQLServerBackups’,
NAME = ‘Full Backup of VgnGoliveMIPL0605’;
GO
i got message, that is sucess.
but i need to what is ‘Z_SQLServerBackups’
Your question is not very clear
What do you want to know?