I'm looking for a solution or workflow. Here's how I used to do database deploys when using a stand-alone server:

  • Create a snapshot of the production database
  • Deploy changes
  • Verify that the deploy was successful
  • If the deploy was not successful, restore the database from the snapshot.

I would like to perform a similar process on a database in an Availability Group, but restoring from a backup or snapshot is not a desirable option because the database is fairly large and I'd have to take the database out of the AG first. Here's the ideal type of solution I am looking for:

  • Suspend data movement in the AG
  • Deploy changes to primary replica
  • Verify that the deploy was successful
  • If the deploy was not successful, rollback the primary replica to the LSN where we suspended data movement and restart synchronization.

Is there a practical way of doing this?

I would use BEGIN/ROLLBACK TRANSACTION, but this is harder to implement with the automated build process, and given that I would first like to inspect the results (from a different database connection) before committing or rolling back.

1 Answer 1


There's not really a practical method. I can really think of three ways of rolling back a failed deployment:

  • BEGIN \ ROLLBACK TRANSACTION: Normally, this is what I'd recommend - it's cleanest, and you wouldn't have to suspend data movement to do it. However, since you need to be able to view the changes from another machine, it's not really an option.
  • Rollback script: With this, you pre-script the commands required to return the database to the state it was in prior to the deployment. It's not a true rollback, and it may be impractical on a large database in some circumstances.
  • RESTORE DATABASE: As you mentioned, there are hassles for this on a large database in an AG.

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.