I have a SQL Always on availability group, with 4 replicas. One of them is the primary. Lets say someone accidentally drops a table on the primary. This transaction would go to all of the secondary replicas.
Now if I want to restore the state of the entire group to before the table was dropped, what would be the correct (time efficient) sequence of steps to take? Assuming I have all the full and transaction backup files needed.
From my current understanding, restoring a back up on the primary alone would cause a data conflict (or corruption issues) on the secondary replicas – so I have to suspend data movement and drop the availability databases on the secondary before I restore a back up, then re-seed the databases from scratch on the secondary replicas. Is this is the best approach? Thanks.
Best Answer
Yes, that is the correct approach.
From my current understanding, restoring a back up on the primary alone would cause a data conflict (or corruption issues) on the secondary replicas
Unless you remove the database from the Availability Group, you will not be able to restore the same database on the primary node.
On a side note: If it is just dropping one table (depending on the size of the table compared to the database size) I might consider restoring the backup with a different name and recreate that table in the primary. There are many ways to do that which is not the scope of this question.