MS SQL Server - Restoring Databases

Restoring is the process of copying data from a backup and applying logged transactions to the data. Restore is what you do with backups. Take the backup file and turn it back into a database.

The Restore database option can be done using either of the following two methods.

Method 1 – T-SQL

Syntax

Restore database <Your database name> from disk =  <Backup file location &plus; file name> 

Example

The following command is used to restore database called TestDB with backup file name TestDB_Full.bak which is available in D: location if you are overwriting the existed database.

Restore database TestDB from disk =   D:TestDB_Full.bak  with replace

If you are creating a new database with this restore command and there is no similar path of data, log files in target server, then use move option pke the following command.

Make sure the D:Data path exists as used in the following command for data and log files.

RESTORE DATABASE TestDB FROM DISK =  D: TestDB_Full.bak  WITH MOVE  TestDB  TO 
    D:DataTestDB.mdf , MOVE  TestDB_Log  TO  D:DataTestDB_Log.ldf 

Method 2 – SSMS (SQL SERVER Management Studio)

Step 1 − Connect to database instance named TESTINSTANCE and right-cpck on databases folder. Cpck Restore database as shown in the following snapshot.

Step 2 − Select device radio button and cpck on elppse to select the backup file as shown in the following snapshot.

Step 3 − Cpck OK and the following screen pops up.

Step 4 − Select Files option which is on the top left corner as shown in the following snapshot.

Step 5 − Select Options which is on the top left corner and cpck OK to restore TestDB database as shown in the following snapshot.