Settings and activity
1 result found
-
20 votes
Our current guidance for rollback support on SQL Change Automation is here: https://documentation.red-gate.com/sca/developing-databases/concepts/advanced-concepts/rollbacks
Our roadmap for 2021 does include further researching and evolving rollback procedures: https://www.red-gate.com/products/redgate-deploy/roadmap
An error occurred while saving the comment An error occurred while saving the comment 
Chris Lambrou
commented
In principle, with each Update.sql script we could also generate a corresponding Rollback.sql script and change report. We could either provide a -Rollback switch to the Publish-DatabaseUpdate cmdlet, or event a separate Unpublish-DatabaseUpdate cmdlet.
In practice, we could only safely perform a rollback after a successful update. I would only recommend using a rollback if it's actually an expected part of an automation process. In that case, I'd advise at least considering the alternatives of using backup and restore, or rolling forward to a new version that reinstates a previous database schema.
Finally, if an update is only partially applied, but then fails, there's no guarantee that the Rollback would even work. Although the Update script includes a transaction which will attempt to rollback changes on error, this isn't foolproof. Not every change can be carried out within the transaction, and it's possible the script was abruptly interrupted anyway (e.g. network failure). The upshot is that your target database will be in an unknown state. At this point, it's probably best to carry out a manual intervention to resolve the issue.
Ah, I see. Yes, that seems like quite a sensible use case.
Including a rollback script is something that's already on our roadmap. It's also attracted quite a number of votes and is probably reasonably straightforward to implement. As such, it's likely to come quite high up our backlog. I'll keep you posted.