How does database recovery work from full + differential + transaction log backups?

Restoring a database from full, differential, and transaction log backups occurs in several steps:

1. The last full backup is restored — this is the baseline state of the database.
2. Then, the latest differential backup is applied, which contains changes since the full backup.
3. After that, all transaction logs made after the differential backup are sequentially applied to bring the database to the desired state at the time of the last log.

It is important to use the NORECOVERY option during intermediate steps so that the database remains in a restoring state and can accept subsequent backups, and only after applying all backups, execute RECOVERY.

Example commands:

RESTORE DATABASE MyDB FROM DISK = 'full.bak' WITH NORECOVERY;
RESTORE DATABASE MyDB FROM DISK = 'diff.bak' WITH NORECOVERY;
RESTORE LOG MyDB FROM DISK = 'log1.trn' WITH NORECOVERY;
RESTORE LOG MyDB FROM DISK = 'log2.trn' WITH RECOVERY;