0

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.

Improve this question

1 Answer 1

Reset to default
4

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.

Improve this answer
2
  • "restoring the backup with a different name and recreate that table in the primary" - meaning, after the backup is restored to a new database, I run a cross database query to re-create the table? or would it be better to generate a "table create script" which includes the data and then running then on the original database?
    – Prashanth Subramanian
    Commented Apr 17, 2020 at 11:44
  • 1
    You can use the import/export tool. Create the table with the same definition and then import the data.
    – SqlWorldWide
    Commented Apr 17, 2020 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.