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.
Best Answer
While not exactly answering your LSN questions, I think you definitely need to revisit your Recovery Point Objective (RPO). How much data are you willing to lose? Assuming good hourly differentials, you should be able to restore back to the latest hour (currently), but you wouldn't really be able to take advantage of any transaction log backups. I'd definitely suggest increasing the frequency of transaction log backups - maybe every 15 minutes. If they take 10 minutes to complete, then that would most likely be the most data you'd lose - assuming your other backups are good (full and diff).
All backups have enough of the log to be able to restore a consistent database by applying committed changes after the backup started and rolling back transactions that had not completed before the backup completed.
Check out Paul Randal's excellent post on Understanding Logging and Recovery in SQL Server.