LSN numbers query regarding Differential backups
0
votes
1
answer
483
views
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.
Asked by zapcon
(73 rep)
Jun 1, 2017, 11:16 AM
Last activity: Aug 7, 2025, 12:04 AM
Last activity: Aug 7, 2025, 12:04 AM