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 + 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.

Management Studio Testinstance

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

Select Backup Device

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

Restore Database

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

Restore Database TestDB

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

Restore Database TestDB
Advertisements