I'm new to DB backup. I just learned about backup types from this question(Link). After I read the differential backup is cumulative since the last full, I got curious if multiple full and differential backups are not mandatory.
For example, I have backups like this:
full_backup_2018_05_09_000000.bak
tran_backup_2018_05_09_000500.trn
tran_backup_2018_05_09_001000.trn
diff_backup_2018_05_09_001500.bak
tran_backup_2018_05_09_002000.trn
tran_backup_2018_05_09_002500.trn
diff_backup_2018_05_09_003000.bak
tran_backup_2018_05_09_003500.trn
tran_backup_2018_05_09_004000.trn
diff_backup_2018_05_09_004500.bak
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn
full_backup_2018_05_09_010000.bak
...
What's the difference between this and using one full backup and transaction log backups like this?
full_backup_2018_05_09_000000.bak
tran_backup_2018_05_09_000500.trn
tran_backup_2018_05_09_001000.trn
tran_backup_2018_05_09_001500.trn
tran_backup_2018_05_09_002000.trn
tran_backup_2018_05_09_002500.trn
tran_backup_2018_05_09_003000.trn
tran_backup_2018_05_09_003500.trn
tran_backup_2018_05_09_004000.trn
tran_backup_2018_05_09_004500.trn
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn
tran_backup_2018_05_09_010000.trn
...
Best Answer
Recovery Time Objective
RTO describes the time taken to perform a full restore of the database to a given point in time.
Restoring a large number of transaction log files will normally be considerably slower than only restoring the most recent differential followed by your transaction logs. Therefore your RTO would be longer.
Fault tolerance
If you lose a single transaction log backup (or that backup becomes corrupted or compromised in some way), your second example would not tolerate that single file being unfit/unavailable for restore, if you were trying to restore to the latest point in time.
The first example you provided requires you to restore only the most recent differential backup and then every subsequent transaction log backup, if you were trying to restore to the latest point in time.
DR Strategy
Often a database can be so large that performing more than 1 full backup per week is difficult to manage, in that the backup may not complete during a period of low activity, therefore the relatively light-touch of differential backups can help. The choice of how/when to do backups in SQL Server is based entirely upon RTO/RPO.