I currently manually create a rollback folder in the project. Then,I create a subfolder nabbed the same as the migration subfolder (e.g 1.1.0-changes). Then, when I import, I copy the revert sql into a rollback script and modify as necessary. Then I import the migration. Then I copy the migration script filename over the rollback script filename and add "-ROLLBACK.sql" to the name.

I made a powershell script to copy all of the rollbacks for a given folder into a single script in reverse order.

I'd love to see this more automated in SCA. :)

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.

Backup/restore isn't a viable alternative when the deployment targets hundreds of databases on a single server (much less thousands of databases across dozens of servers).

The "failure" may not be schema at all, but rather the discovery that the application driving the schema change has just deployed a fatal error and needs to roll back to the previous release of code/schema. For this reason, any Update.sql we create comes with a corresponding Antidote.sql to undo that update, so far as is possible (e.g. columns dropped can't be undropped).

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.