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.

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.

MS SQL Server - Creating Backups

 Backup is a copy of data/database, etc. Backing up MS SQL Server database is essential for protecting data. MS SQL Server backups are mainly three types − Full or Database, Differential or Incremental, and Transactional Log or Log.

Backup database can be done using either of the following two methods.

Method 1 – Using T-SQL

Full Type

Backup database <Your database name> to disk = '<Backup file location + file name>'

Differential Type

Backup database <Your database name> to 
   disk = '<Backup file location + file name>' with differential

Log Type

Backup log <Your database name> to disk = '<Backup file location + file name>'

Example

The following command is used for full backup database called 'TestDB' to the location 'D:\' with backup file name 'TestDB_Full.bak'

Backup database TestDB to disk = 'D:\TestDB_Full.bak'

The following command is used for differential backup database called 'TestDB' to the location 'D:\' with backup file name 'TestDB_diff.bak'

Backup database TestDB to disk = 'D:\TestDB_diff.bak' with differential

The following command is used for Log backup database called 'TestDB' to the location 'D:\' with backup file name 'TestDB_log.trn'

Backup log TestDB to disk = 'D:\TestDB_log.trn'

Method 2 – Using SSMS (SQL SERVER Management Studio)

Step 1 − Connect to database instance named 'TESTINSTANCE' and expand databases folder as shown in the following snapshot.

Step 2 − Right-click on 'TestDB' database and select tasks. Click Backup and the following screen will appear.

Step 3 − Select backup type (Full\diff\log) and make sure to check destination path which is where the backup file will be created. Select options at the top left corner to see the following screen.

Step 4 − Click OK to create 'TestDB' database full backup as shown in the following snapshot.