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 + 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 like 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:\Data\TestDB.mdf', MOVE 'TestDB_Log' TO 'D:\Data\TestDB_Log.ldf'

Method 2 – SSMS (SQL SERVER Management Studio)

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

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

Step 3 − Click 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 click OK to restore 'TestDB' database as shown in the following snapshot.