2

My current backup strategy for our AlwaysOn clusters is a daily full backup and hourly transaction log backups via a third party backup program. We had a request to minimize the amount of log backups and I noticed that we had a cluster of large log backups at the same time during the day and we began a process to run a log backup to nul right before a full backup.

It seemed to have worked until I tried to do a point in time restore and then it wouldn't restore past the full backup. I investigated and noticed that there is an overlap in the LSN's being backed up. Here is an example for one of the databases.

Last real log backup (secondary) - 20165000011169700001 / 20168000018974100001

backup to nul (primary)          20168000018974100001 / 20168000019060800001

full backup   (primary         20168000019060800001 / 20168000019245900001

first log backup (secondary)      20168000019060800001 / 20174000018926600001

It looks like like the log records backed up to the nul device isn't being registered on the secondary node of the cluster. I assumed that running backup log on any node will transfer to the other one since we normally run log backups on the secondary.

Is this how it is supposed to work?

Improve this question
2
  • Question, why run the backup to nul on the primary instead of the secondary? And why split the Full from the Log across different replicas? Just curious.
    – S3S
    Jun 29, 2018 at 20:55
  • The AlwaysOn policy is backup on secondary. Before we put the backups onto Commvault we had an hourly Backup to Null job which I changed to run once daily and just enabled it on the primary assuming it would be OK since you can run log backups on any member.
    – Alen
    Jun 29, 2018 at 21:00

1 Answer 1

Reset to default
1

it looks like like the log records backed up to the nul device isn't being registered on the secondary node of the cluster.

Not to me. The First Log Backup on the secondary (after the full) starts with the last LSN covered by the backup to 'nul' taken on the primary.

But stepping back to "We had a request to minimize the amount of log backups": why? And why are you breaking the log chain with the backup to 'nul'?

Is this how it is supposed to work?

You are supposed to maintain an unbroken chain of log backups in addition to occasional Full backups. The log records included in a Full backup will also be present in the next Log backup, as Full backups never break the log chain. This allows you to loose a Full backup, and restore from a previous Full plus all the log backups.

Improve this answer
7
  • I'm doing a backup to nul and then a full backup within 15 minutes. So it's not really a broken chain because there aren't any more regular log backups after the backup to nul. We do something like 200GB in log backups daily and there is an issue with the storage requirements. My goal is to take the last log backup, have the batch processes run and then take a full backup capturing all the changes in the full without the huge log backup afterwards.
    – Alen
    Jul 2, 2018 at 12:28
  • Well that won't work, as the Full Backup has no effect on the Log Backups. Each Log Backup will have all the log records since the last log backup, regardless of whether you have some Full Backups in between. Why not just delete the older log backups?
    – David Browne - Microsoft
    Jul 2, 2018 at 12:37
  • That's the whole point of the backup to nul. Run the last log backup 2-3 hours before the scheduled full, run processes, run backup to nul and then run the full backup. This way I capture everything up to my batch processes, and those are picked up in the full backup. And I'm reducing the size of my log backups since most of them happen after the batches run. I made a few changes to the backup to nul job on friday and the LSN's seem to be OK now, but i'll probably have to run a few test restores to check.
    – Alen
    Jul 2, 2018 at 12:48
  • It would be safer to retain the last log backup until after your Full backup is complete, instead of breaking your log chain before you have a Full.
    – David Browne - Microsoft
    Jul 2, 2018 at 12:54
  • The period between the last real log backup and the backup to nul is scheduled processes which we can always rerun if we had a problem instead of trying to restore from backup. Even then the size of some of the log backups are so large we'll never meet an SLA to restore them in time and it's faster to simply rerun the process.
    – Alen
    Jul 2, 2018 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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