Web Designing, Web Development, Website Design, Website Development, SEO

0091 98233 72069

COGS: CUSTOMISED, OPTIMISED, GENERALISED WEB DESIGN & WEB DEVELOPMENT SOLUTIONS

Restore MSSql Server database

There are two ways to restore database

1. Using SQL statement

2. Using visual database import / restore feature in sql server manager.

Restoring database using .bak file from one server to other server some times may give issue in visual interface. Good option is use sql statement. Below are two different ways to do this as explained below. Second Method is easier to follow

A) FIRST Method

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
B) SECOND Method
RESTORE DATABASE YourDB FROM DISK = 'D:\BackUpYourBaackUpFile.bak'
WITH REPLACE
GO