MS SQL Server – Restoring Databases


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 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: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-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

”;

Leave a Reply

Your email address will not be published. Required fields are marked *