A backup chain is a specific sequence in which all database backups should be restored. If 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.
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.
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.
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.
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.