SQL Server High Availability restore copy_only backups from secondary replica problem

Question

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?

Answer 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.