June 11, 2026 at 1:52 am
Please I am hoping for some feedback
Specifications:
Windows Server 2022
SQL Server 2022
Availability Group with 4 replica
I am trying to make sure I am using the best method of backing up my databases. I cannot use the Ole Hollengren jobs because of my environment
Currently I have all four nodes set up with FULL, DIFF and TRN backups using SQL Maintenance plans. I have the backup preference set to primary and there are no copy only settings. All backups are using two folders depending on which node is primary. Node 1,2 backup to folder A, Node 3,4 backup to folder B. Backup jobs are going fine and when there is a fail over the backup run on the correct folder - no problems
I am doing FULL on Saturday, DIFF Daily, and TRN every 30 minutes. When there is a fail over the DIFF and TRN backups start up in the new folder just fine.
Q: am I crazy for setting up my backups this way
My restore plan is to take the FULL, DIFF and TRN files move them to a folder on the server and then do a restore using the GUI in SSMS,
Today I tried to perform a test restore, so I basically take a FULL, DIFF, and a few TRN backup files and move them to a TEMP folder. I then do the restore and choose the backup files. Well I have tested this in the past and it worked just fine - the restore can see all the backup files - FULL, DIFF and TRN without issue.
Today I kept getting an error - ...Full backup not available sorry I do not have the exact error message
Q: Is the backup chain broken when a fail over occurs?
Your feedback is appreciated.
Jeff
June 12, 2026 at 2:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 19, 2026 at 10:03 am
Q: am I crazy for setting up my backups this way
A: whatever works for you, and your HA/DR requirements. Depends so much on what infra is available to you (NAS, SAN, Cloud Blob, etc) and on your backup policies.
Q: Is the backup chain broken when a fail over occurs?
A: you would need to take the FULL, DIFF and TRNs from the same Primary Node; maybe the Primary switched from 3-4, for example, meaning the FULL was from 3, but the DIFF/TRN was from 4?
July 19, 2026 at 9:01 pm
Hi! Great questions, and no, you're not crazy. Let me take these one at a time.
Q1: Is this setup crazy?
Not at all. Maintenance Plans plus Backup preference = Primary is a perfectly valid pattern for AG backups when Ola Hallengren isn't an option. The fact that your DIFF/TRN jobs correctly pick up on the new primary's folder after a failover tells me you've already got the important piece in place: something (likely an IF sys.fn_hadr_backup_is_preferred_replica(DB_NAME()) = 1 check, or equivalent, in your job step) is gating the backups so only the preferred replica actually runs them. That's the #1 thing people get wrong with native Maintenance Plans on an AG, since plain Maintenance Plans have no built-in AG awareness, unlike Ola's scripts. If you've got that working across 4 nodes and 2 folders, you're already ahead of a lot of setups I've seen.
One thing worth double-checking while you're in there: is there any other process (a third-party backup/VSS agent, a monitoring tool, someone's old ad-hoc script) that could also be issuing a native BACKUP DATABASE against these databases without COPY_ONLY? That ties directly into your second question.
Q2: Does failover break the backup chain?
Short answer: no, not by itself. The differential base LSN and the full LSN chain are properties of the database, not the instance that happened to run the backup. That state travels with the data through the AG's log stream. So a FULL taken on Node 1 last Saturday, followed by a DIFF taken on Node 3 today after a failover, is still a perfectly valid pair as far as SQL Server is concerned. A clean automatic/synchronous failover doesn't truncate or reset any of that.
What does break down across nodes is msdb backup history: it's local to whichever instance ran the backup. Node 3's msdb has zero knowledge of the FULL backup Node 1 took. That mostly matters for tools that build a restore plan from history rather than from the files themselves, but you said you're pointing SSMS at the actual files, so that shouldn't be your issue directly.
Given that, "Full backup not available" when you're restoring from files almost always means the DIFF you grabbed isn't actually based on the FULL you grabbed, i.e. something reset the differential base in between (another full backup ran that you didn't account for) and the DIFF file's embedded base LSN no longer matches. This is very easy to hit by accident when you're manually hunting through two separate folders across a failover window. Worth confirming with a quick check rather than assuming the AG did something wrong:
RESTORE HEADERONLY FROM DISK = N'D:\Temp\YourFull.bak';
RESTORE HEADERONLY FROM DISK = N'D:\Temp\YourDiff.bak';
-- compare DifferentialBaseLSN on the diff row against CheckpointLSN on the full row
If those don't match, you've found your culprit. It's worth checking all four nodes' msdb..backupset (or a script that pulls Get-DbaDbBackupHistory across all replicas via dbatools, since that gives you one unified view instead of hopping between four separate msdb databases) to find out what actually ran a full backup and when.
Since you're already doing manual test restores by hand, honestly that's the right instinct, most people skip it. It might be worth scripting that into something repeatable so you get a timestamped, auditable "yes, this chain restores cleanly" result every time instead of re-verifying by hand. Happy to point you at how I do that if useful.
July 20, 2026 at 2:02 am
Thank you very much for the feedback
So the issue with the below error
Today I kept getting an error - ...Full backup not available sorry I do not have the exact error message
Turned out that I had made a FULL backup during the week on Wednesday and was trying to restore from the FULL backup done on Saturday. The Wednesday FULL broke the chain. I did the restore using the Wednesday FULL and it worked
I would like to add additional information to this post. So starting with SQL Server 2022 Microsoft gives you the option of using a container set of system databases within the availability group that makes creating maintenance jobs and restoring much easier. With those container system databases you only have to create the one maintenance plan instead of creating a maintenance plan on each node. Also restore history is in that msdb in the AG so its all there
The drawback to those contained databases/maintenance plans is that it will NOT backup any of the stand alone databases, so in essence you end creating more maintenance plans to account for any stand alone databases.
Thank you very much for your time on this subject.
Jeff
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply