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.

Rolling back vs rolling forward

There are two patterns to take the database back to a previous schema state - rolling back and rolling forward.

• Rolling back restores a previous schema state as if the later schema state never happened. For example, 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).

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 (see this tutorial), 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.

If you save to the snapshot history table on deployment and configure the history limit to enable multiple entries, you can generate a rollback script ad-hoc to get back to a previous version. See this tutorial. This avoids generating undo scripts up front and persisting them, but snapshots do not currently support static data, while undo scripts do.

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.

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, you can roll forward by copying 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 the snapshot history table

If you save to the snapshot history table on deployment and configure the history limit to enable multiple entries, you can generate a new migration script to get back to a previous version. See this tutorial.

This avoids generating undo scripts up front and persisting them, but snapshots do not currently support static data, while undo scripts do. You still need to handle any changed repeatable migrations manually.

Rolling forward using Redgate comparison technology

If undo scripts aren't being maintained and there is no snapshot history, 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. See this tutorial.

Alternatively, if you save to the snapshot history table on deployment and configure the history limit to enable multiple entries, you can generate an undo script retroactively to get back to a previous version. See this tutorial.

Executing the rollback

You can execute an undo script using the undo command. By default this will just execute the most recent undo script, but you can roll back further in one go by specifying the target version,

Note that repeatable migrations cannot be handled using undo. If you need to roll back changes to repeatable scripts, you will need to retrieve older versions of these scripts using version control and execute migrate.

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.

Relevant tutorials

• Tutorial - Roll back using undo migrations
• Tutorial - Roll back a state-based deployment
• Tutorial - Roll back or roll forward by generating scripts ad-hoc using snapshot history