How to Restore SQL Server Database Backup using SQL Query?

Restoring a MS SQL database on another server or from another server can be very tricky. You may more than often encounter errors while trying to restore a MS SQL database through the SQL Server Management Studio. Most of the times the errors occur because of any of the following reasons

1. The Logical file names may be different.
2. The physical path may be different.

In such scenarios, we restore databases using SQL query. Following example shows how to restore a database using SQL query.

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 FILELISTONLY
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
GO

Step 2: Use the values in the LogicalName Column in following Step.

RESTORE DATABASE YourDB
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.ldf’,REPLACE

The above query will not only move the logical file name of the database to the physical path of the MDF and LDF files, but also replace them.

If you still face errors while restoring the database using the above method, set the database into single user mode using the following query.

ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

Then restore the database using the above restore query. Once the database is restored successfully, convert the database into multiuser mode using the below query.

ALTER DATABASE YourDB SET MULTI_USER
GO