Friday, December 7, 2012

SQL SERVER – Restore Database Backup using SQL Script (T-SQL)



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

No comments:

Post a Comment