It is important to consider how you might handle a scenario in which a deployment needs to be reversed (or "rolled back"). With application code, rolling back to an earlier version is pretty straightforward: you can simply replace the deployed artifacts with the previous working version. Database rollbacks are more problematic due to the nature of persistent storage.

Note: Rollback scripts (aka undo scripts) are no substitute for having a robust backup/restore strategy. Relying solely on rollback scripts is not on its own a viable strategy as certain changes, such as table or column drops cannot be recovered directly, and will required a backup to be restored to recover dropped data.

Rolling back vs rolling forward

There are two patterns to take the database back to a previous schema state - rolling back and rolling forward. It is important to understand how they differ in order to understand which approach to take in a given situation.

• Rolling back restores a previous schema state as if the later schema state never happened. For example, for if we roll back a database that has recently been upgraded from v3 to v4, the database will see itself as being at v3.
• Rolling forward acknowledges that the later state (v4) occurred, and migrates ahead to a new distinct version (v5) that just happens to correspond to a previous state (v3). Using the same example, rolling forward would take us from v4 to v5, where the v5 state represents exactly the same structure for objects as in v3.

When should I roll back and when should I roll forward?

State-based deployments

For state-based deployments, there is little distinction between rolling back and rolling forward.

You can generate undo scripts while generating deployment scripts, but there is no difference in output between executing one of these undo scripts, versus generating a new deployment script to return to the previous state and executing that.

For examples of using undo scripts see common state-based deployment scripts. An alternative to undo scripts is to capture a database schema snapshot before release and use that as the basis for generating a rollback script.

Migrations deployments

For migrations-based deployments, neither rolling back nor rolling forward is better than the other - which one should be employed depends on the scenario.

Rolling forward

Rolling forward is no more than a new deployment that reverses changes made by the previous deployment.

As a general rule, when reverting database schema changes made to any live production database, it is not only simpler to roll forward, it also maintains the deployment audit trail which is all important for compliance purposes. For more detail, see this guidance,

An exception is when the change being rolled back leaves the database in an undesired or even dangerous state. What is to be avoided is that these errant migration script are run again in future, which would occur if there are multiple product databases, or even as part of standing up development and test databases. In such cases, rolling back and deleting the problematic migration scripts would be the right approach.

A roll forward can simply be regarded as a new migration, and should therefore follow the same release procedure.

Rolling forward using undo scripts

If undo scripts are being maintained already, the simplest way to roll forward is to copy the undo script as the next migration script. If there are repeatable migrations that need to be reverted, these will need to be copied from the previously deployed release package.

If not all of the changes need to be reverted, it is simply a question of editing the migration script and removing changes that are to be preserved. Likewise for repeatable migrations, only copy those that need to be reverted.

Rolling forward using Redgate comparison technology

If undo scripts aren't being maintained, the roll forward script will need to either be authored manually or generated using Redgate Compare technology.

Rolling back

Rolling back reverts to a version "back in time" as if the deployment had never happened. This means that unlike with rolling forward, it is possible to modify and even delete migration scripts higher than the current version.

Rolling back is appropriate in the following situations:

• When reverting database changes made to a production database that has never been live (e.g, the database has never left the downtime window), it may be acceptable to roll back, as the database has effectively been frozen in time with no new transactional data entering the system.
• When the state left by an errant migration is deemed destructive to the extent that it would be imprudent to allow future deployments to transition through this state. Future deployments could be to different production databases, or when standing up dev or test databases.
• If a local development or test database needs to brought back to a previous version, rolling back is a good approach. This can be required for troubleshooting purposes or to test a forward migration script, for example.
• Rolling back can be employed as part of a testing process in order to ensure that a deployment is recoverable. For example, in a automated testing process a test database could be upgraded, rolled back and upgraded again, with various checks to ensure the schema state is correct at each stage.

Rolling back involves:

1. Running pre-authored undo scripts (prefixed with U), starting with the most recent, all the way back to the undo script that corresponds to the version to be rolled back to.
2. Running the repeatable migrations corresponding to the version to be rolled back to.

After rolling back, you may choose to then alter undeployed migration scripts to prevent future issues.

Authoring undo scripts

For rollbacks to work, an undo migration needs to be maintained for each forward versioned migration between the currently deployed version and the version to roll back to.

Executing the rollback

To execute the rollback, call the following flyway commands in sequence.

flyway undo -target=123 -configFiles=<yourprojectconfigfile.conf>

This runs all undo scripts starting from the current release, version 129, back to version 123.

flyway migrate -target=123 -configFiles=<yourprojectconfigfile.conf>

This will run the repeatable migrations, bringing the programmable object versions back to the state they had been in version 123.

Note: Remember to apply the rollback to all environments where the errant migration script has run.

Deleting migration scripts post-rollback

In cases where a rollback has been applied because of an unwanted (eg, destructive) migration script, it will be necessary to delete the migration script to ensure it doesn't run again.

Note: If there are development or test environments where the errant migration script remains applied, this will also need to be rolled back. If not, it will fail its validate operation, showing the status as future because a "future" migration is now missing. At this point, there are two options:

1. Drop and recreate the dev/test environment.
2. If only the "future" migration script has been deleted, but not the corresponding undo script, it is possible to run Flyway undo to effect the rollback.

Generating a rollback script using snapshots

It's also possible to generate a rollback script as part of your automated pipeline using our comparison technology.  Take a snapshot of the production database before doing the release.  Then compare production (or a post-release snapshot) to the pre-release production version to find the differences and generate a sql script to undo these.  If you run this script, the flyway_schema_history table will still show the V and R scripts as released and they will not be re-run.

This can also be achieved using scripts in Flyway Callbacks:

1. Use the beforeMigrate callback to call the flyway snapshot command to capture the current state of the database before deploying to it.
2. Use the afterMigrate (or afterMigrateError) callback to compare the target database to the snapshot to generate a script that can be used as a starting point for recovery, if needed.