A rollback applies a reverse migration that brings back a historic database state. If a forward migration involves data loss, a rollback might not be able to recover it and recovery from backups may be the only solution.

There are circumstances where rolling back is appropriate:

1. Many organizations require that a rollback strategy is in place and validated prior to applying a release. It can be practical to apply a rollback as part of a test scenario, either manually or automated.
2. In the case of a failed deployment, a rollback might be appropriate. However, if the state of the database has changed since the release, it is highly recommended to use a roll forward strategy instead.

Rolling back using Redgate Change Automation

Redgate Change Control projects are made up of a collection of versioned migrations (prefixed with V) and repeatable migrations (prefixed with R). Versioned migrations often describe changes to table structures, often involving data motion, and repeatable migrations are used to update changes to programmable objects such as functions and procedures.

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.

Authoring undo scripts

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

Undo scripts need to reverse the change made by the corresponding versioned migration and ensure that any data manipulation is reversed.

Undo scripts should be named the same as their corresponding versioned migration, except using the U prefix instead of V.

Eg. V123__SomeChange.sql and U123__UndoSomeChange.sql

Assembling a rollback package

To roll back, you will need to construct a "rollback project" containing the right combination of undo scripts and repeatable migrations.

Although the latest deployed release package produced by Redgate Change Automation will contain all the necessary undo scripts, only the latest version of the programmable objects exists as repeatable migrations. Therefore, a rollback package needs to be assembled (and ideally automated). In this example, we will roll back from version 129 to version 123.

1. Create a copy of the release package and extract the contents into a newly created folder, for example Rollback-V123
2. Delete all of the repeatable migrations from the sql folder (eg, recursively delete all files beginning with "R__")
3. Copy into the sql folder all of the R__ objects from the sql folder from the historic release project that you want to roll back to, in this case the release project corresponding to version 123.

The Rollback-V123 project folder is now equipped with the necessary undo scripts and repeatable migrations to rollback to version 123.

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. This is because Redgate Change Automation 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.