Monday, July 18, 2011

SQL SERVER – Restore Database Backup using SQL Script

Database YourDB has full backup YourBaackUpFile.bak. It can be restored using following two steps.
Step 1: Retrive the Logical file name of the database from backup.
RESTORE FILELISTONLYFROM DISK = 'D:BackUpYourBaackUpFile.bak'GO
Step 2: Use the values in the LogicalName Column in following Step.
----Make Database to single user ModeALTER DATABASE YourDBSET SINGLE_USER WITH
ROLLBACK 
IMMEDIATE
----Restore DatabaseRESTORE DATABASE YourDBFROM 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

No comments: