SQL Server Availability Group Database Backup Missing Data

Question

Post reply

SQL Server Availability Group Database Backup Missing Data

KathyGibson96

SSC-Addicted

Points: 484
More actions
August 22, 2024 at 8:04 pm

#4448453
I have a production SQL2019 Enterprise SQL Server which hosts an Availability Group with 12 small to medium databases in it.
This is an OLTP server and there is no database over 200gb.
The AG only has 2 nodes and is used for H/A when patching. Both nodes are onPrem so the Availability Mode is Synchronous Commit.
I performed a full backup today and restored the backup to Test (as part of this process I set the recovery model to simple).
After the restore, I noticed that data from 1 of our historical tables was over a month behind and that I was also missing some recently deployed schema changes.
Does anyone have any insight into what could have caused this? I was expecting data and schema to be the same as when the backup was taken.
Thx
- This topic was modified 3 months ago by KathyGibson96.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

KathyGibson96 wrote:

I have a production SQL2019 Enterprise SQL Server which hosts an Availability Group with 12 small to medium databases in it.
This is an OLTP server and there is no database over 200gb.
The AG only has 2 nodes and is used for H/A when patching. Both nodes are onPrem so the Availability Mode is Synchronous Commit.
I performed a full backup today and restored the backup to Test (as part of this process I set the recovery model to simple).
After the restore, I noticed that data from 1 of our historical tables was over a month behind and that I was also missing some recently deployed schema changes.
Does anyone have any insight into what could have caused this? I was expecting data and schema to be the same as when the backup was taken.
Thx

What was the state of secondary databases when you did the backup?

rothj Hall of Fame Points: 3407 More actions · Answer 3

About the only time I see something like this is when I grabbed the wrong backup (an older one). You are certain you grabbed the correct backup file? Did you overwrite the file? Are there multiple backups in the same file where you could have restored an older backup?