2

I've implemented the Ola Hallengren backup solution across our 12-instance farm with AlwaysOn High Availability. Typical setup of weekly full backups, daily differential backups and 4-hourly transaction log backups.

To leverage performance on the Primary, I've offloaded these backups to the secondary. This makes the Full backups copy_only on the secondary. In theory the Diff backups shouldn't occur, but the job just runs on the primary with the Diff backups also marked as copy_only. Log backups run on the Secondary.

Thought I'd better test the restore process. Restored a copy_only Full backup WITH RESTORE and MOVE to a test database and that was fine. Deleted the test database and restored the copy_only Full backup WITH NORESTORE and MOVE to a test database followed by a restore of the next copy_only Differential backup. This failed with

"the differential backup cannot be restored because the database has not been restored to correct earlier state."

Looking at sys.database_files it shows differential_base_time of 2017-02-15 and a differential_base_LSN of 467000002068600193. I do not have this file!

Using RESTORE HEADERONLY my copy_only Full backup has a

FirstLSN = 743000000530200000,

LastLSN = 743000000569700000,

CheckpointLSN = 743000000530200000, DatabaseBackupLSN = 467000002068600000

My copy_only Differential backup has

FirstLSN = 788000000036000000

LastLSN = 792000000047100000,

CheckpointLSN = 792000000040600000, DatabaseBackupLSN = 467000002068600000

DifferentialBaseLSN = 467000002068600000

Irrespective of the potential disaster of not having a Full backup covering LSN 467000002068600193 (or 467000002068600000...), I expected to see some connecting chain between LastLSN of my copy_only Full backup and the FirstLSN of my copy_only Differential backup. This doesn't seem to be there. Can someone explain the gap in my understanding please?

And can someone recommend my least painful way of getting my backup strategy back in line?

If offloading backups to a secondary replica makes all Full backups copy_only with no ability to capture the DifferentialBaseLSN and does not allow Differential backups at all (seemingly), can someone also explain any significant benefit of such offloading?

1

First things first, get rid of the differential backup. If you are performing full, copy only backups off of a secondary these will do nothing for you.

The differential backup works to backup changes from a full backup (not copy only). When a normal full backup runs it sets all of the pages in the database as being backed up. Going forward from that point any change to a page gets marked, and then the differential would come along and backup any page marked as changed. As time goes along the differential will get larger and larger. A copy only backup does not reset those flags, so things will just get worse.

At this point decide how you want to proceed with your backup strategy. If you want to continue to take differential backups, then also take full backups on your primary. If you no longer want to take the differentials for the database that are in the AG then set the copy_only flag to N in the Ola script. This will allow the differential job to run and take backups of databases not in the AG, but will ignore those that are in the AG (so long as the backup preference marks a secondary replica as the machine to backup from).

As regards the benefit of offloading backups to a secondary, this can help reduce resource contention on your primary replica, which is where most of your traffic can be. It can also be useful as a way to take database backups in a DR site, where you may have a secondary replica or two.

  • Thanks for the info. I need the diff backups as I have several 120GB+ databases and need to ease storage issues on the backup file server. Seems I'll need to change the backup preference to Primary to enable the schedule I'm looking for. Resource contention should be minimised by the weekday Diff and weekend Full backups taking place out of 'normal' working hours. – DiamondBeezer Mar 31 '17 at 8:36
  • If I get rid of the daily Differential backups, are you suggesting replacing with daily Full copy_only backups? Or stick with weekly Full copy_only backups, increase the retention of the Log backups and hope I don't have to restore a whole set of them on a Friday? – DiamondBeezer Mar 31 '17 at 12:54
  • I would go with daily full backups if doing copy only. The time to restore 3 days of logs can be excessive (I'd recommend testing this). That being said, it depends on the amunt of time you can be down. If the business has agreed to a maximum of 6 hours downtime, and you can restore a full and all the logs in that time then it may be just fine. If you don't have RTO numbers it is time to work with the business and figure out what those might look like. – Nic Mar 31 '17 at 15:44

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.