A backup chain is a specific sequence in which all database backups should be restored. If a backup chain is broken, the restore process will be not possible, and some data will be lost. Let us consider the following example with all three backup types to understand the backup chain better.
Example of Database Recovery
Many know that in order to minimize the risk of data loss, it is advisable to use the full recovery model, which allows making transaction log backups. In their turn, transaction log backups enable database restoration to a particular point-in-time. Let’s consider the following sequence of database backups that form a database backup chain. It is assumed that the full database backup is made every 24 hours at 00:00, differential – every 6 hours, and transaction log backup is created every hour. 
Full Backup
Suppose some crucial data were deleted at 13:30. The best way out of this situation is to restore the database to the statement at 13:29. You should begin the restore process of this database from the last full database backup restore operation:
RESTORE DATABASE your_database FROM DISK = 'full_00_00.bak' WITH NORECOVERY, REPLACE
Differential Backup
After the full backup was restored the next necessary step would be restoring the last differential backup. If you are not sure which differential database backups belong to the restored full backup, you can first restore the headers of backups. Please use the following syntax to do it:
RESTORE HEADERONLY FROM DISK = 'full_00_00.bak' RESTORE HEADERONLY FROM DISK = 'diff_06_00.bak' RESTORE HEADERONLY FROM DISK = 'diff_12_00.bak'
The following result will be received:
In this scenario you need to pay attention to the full backup CheckpointLSN value and compare it with Differential backups DatabaseBackupLSN value – they should be identical (marked as blue square). It means that these two differential backups belong to this full backup and can be restored after it. To find out what differential backup was made earlier, compare their FirstLSN numbers (marked as orange square). The last differential backup will have bigger value.
If you try to restore a differential backup before restoring its full backup, you will get the following error message:
Msg 3136, Level 16, State 3, Line 1 This differential backup cannot be restored because the database has not been restored to the correct earlier state. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
That means that the backup chain was broken and any further recovery process is not possible. Therefore, pay attention to what differential backup should be restored after the full backup. In our example it is necessary to restore a differential database backup from 12:00 using the following command:
RESTORE DATABASE your_database FROM DISK = 'diff_12_00.bak' WITH NORECOVERY
Transaction Log Backups
Now that we’ve restored the full and differential backups, let’s restore the database to its state as of 14:29. This is where we will require the transaction log backups. In this case it is also necessary to follow a certain sequence to avoid breaking transaction log backup chain.
If you need to determine what transaction log backup follows after another, you need to get backup’s headers (same as we did with differential backups):
This picture shows a transaction log backup chain, each backup from the chain has its own FirstLSN and LastLSN. Every LastLSN value of the transaction log must be the same as FirstLSN values of the next transaction log backup. These values help to restore transaction log backup files in the right sequence. To find out what transaction log backup should be restored after the differential backup, compare their DatabaseBackupLSN values – they should be identical:
To understand which transaction log backup should be restored first after differential backup, please check LastLSN value of the differential backup. This value should be somewhere between the FirstLSN and the LastLSN values of the transaction log backup. Otherwise, if the wrong transaction log backup is applied to the differential backup, you’ll get the following error:
Msg 4305, Level 16, State 1, Line 17 The log in this backup set begins at LSN 74000000071200001, which is too recent to apply to the database. An earlier log backup that includes LSN 74000000065600001 can be restored. Msg 3013, Level 16, State 1, Line 17 RESTORE LOG is terminating abnormally.
To restore transaction log use the following syntax:
RESTORE LOG your_database FROM DISK = 'log_13_00.bak' WITH NORECOVERY
Then to restore the database up to the state at 13:29:59 run the foloowing command for transaction log from 14:00 using “WITH STOPAT” option:
RESTORE LOG your_database FROM DISK = 'log_14_00.bak' WITH STOPAT = '2015-11-19 13:29:59.000', RECOVERY
