We have the following backup regime in place which I am not 100% sure about.
- Full backup each night at 18:00 taking approx. 6 hours
- Transaction log backup upon completion of full generally about 10 mins.
- Differential backups each hour taking approx. 20 mins.
When querying the msdb.backupset table, I understand that the first_lsn will only be reset by a transaction log backup, but am slightly confused that the last_lsn entry is incremented each time the differential backup runs.
This seems to imply that the differential backup also contains transaction log entries (which I don't believe it does).
Is this entry in msdb.backupset just to give information to a restore to which transaction log backup to use to preserve the chain of transactions ?
Finally, if we only have a transaction log backup once a night and differentials throughout the day, we are still looking at potentially a maximum of 24 hours data loss despite hourly differentials.
Apologies if this seems like a lot of questions but trying to get this clear in my head.
Thanks for listening.