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."
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!
RESTORE HEADERONLY my copy_only Full backup has a
FirstLSN = 743000000530200000,
LastLSN = 743000000569700000,
CheckpointLSN = 743000000530200000, DatabaseBackupLSN = 467000002068600000
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?