Sample Header Ad - 728x90

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