SQL Server Backup and Restore strategy – Part 2

Continuing from my earlier post about backup strategy I will explain on how to restore database from full, differential and log backups.

Assuming we have four backup files on disk which are testdb_full.bak, testdb_differential.bak, testdb_log_1.bak, testdb_log_2.bak. These files were created during backup processes with its sequence are full, differential, first log and second log.
We name the database with ‘TestDB’.

First of all open SQL Server Management Studio and restore the full backup with norecovery option with below syntax:

restore database TestDB From Disk='D:\sqlbackup\testdb_full.bak' with norecovery

Restoring differential and logs processes need norecovery option. However this option will make database unavailable to use by user.
If we restore full backup without this option and later when restoring differential or log then it will fail.

Secondly, restore the differential backup file also with norecovery option with below syntax:

restore database TestDB From Disk='D:\sqlbackup\testdb_differential.bak' with norecovery

Restoring differential backup syntax is same with full. SQL server will automatically know whether it is full or differential type.

Third, restore log backup files sequences:

restore log TestDB From Disk='D:\sqlbackup\testdb_log_1.bak' with norecovery
restore log TestDB From Disk='D:\sqlbackup\testdb_log_2.bak' with recovery

Please take attention on last log restore it uses recovery option. Because of testdb_log_2.bak is the last backup file we have then we need to make database available for normal operation.

Agung Gugiaji

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s