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