0

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.

Improve this question
0

1 Answer 1

Reset to default
0

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.

Improve this answer
6
  • Basically what I I'm trying to say is that I'm not sure how it's possible to use a differential backup to restore to the latest hour if a transaction log backup hasn't been taken for 8 hours. How will the transactions be up to date ?
    – zapcon
    Jun 1, 2017 at 13:36
  • Assuming your backups (FULL/DIFF) are good you can always restore to the latest DIFF without even using any transaction logs - in fact, your transaction logs seems to be useless for recovery purposes since you are taking them immediately after a full backup. Transaction logs are used to get you as close as possible to your RPO - that's why taking frequent transaction logs backups are so important for your RPO.
    – Scott Hodgin - Retired
    Jun 1, 2017 at 13:41
  • Your differential backups will contain all of the updates that occurred during the previous hour (actually all of the update since your last full). So, only using full and latest diff, you would lose an hour at most. Adding frequent transaction log backups to this mix (say every 15 minutes) gets you from losing an hour to possibly only losing 15 minutes.
    – Scott Hodgin - Retired
    Jun 1, 2017 at 13:46
  • Frequent log backups also allow you to possibly bridge the gap should a differential backup be no good - you'd just have to restore more transaction logs. You can actually prove all of this in a small database by taking fulls and diffs and log backups while making small changes in the database - then you'd try various restore scenarios will fulls, diffs and logs to see how you restore as close as possible in the event of a disaster.
    – Scott Hodgin - Retired
    Jun 1, 2017 at 13:51
  • The reason your transaction log backups are taking 10 minutes is because you haven't backed it up in 24 hours (since your last full). If you ran transaction log backups every minute, you would only lose 1 minute should a disaster occur - yes, you'd have to restore more transaction logs, but you should dynamically generate those restores anyway - there are plenty of internet posts on generating the restore commands for transaction logs - again, you should consider what is acceptable data loss (RPO)
    – Scott Hodgin - Retired
    Jun 1, 2017 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.