When contingency planning for your deployments, it's important to consider how you might handle a scenario in which a rollback is required.

With application code, rolling back to an earlier version is pretty straight forward: you can simply replace the deployed artifacts with the previous working version. Database rollbacks are hard due to the nature of persistent storage: the data within your schema must be preserved when rolling forward to allow a prior state to be restored at a later date (if a simple db backup/restore is not an option).

In essence, how you plan to roll back in the event of release failure entirely depends on the changes made to both your application code and database.

There are a selection of options to choose from when choosing a rollback strategy when using Redgate tools.

Rolling back using state

Irrespective of whether state or migrations is being used for deployment, there are many reasons to roll back using a state-based approach.

• It is conceptually simple
• It imposes no additional overhead on the developer or DBA
• There's a good chance that your existing versioning and release setup already allows you to rollback via state

However, there are some caveats:

• State-based rollback will work in a migrations context only if the state schema model is also being saved with the project.
• State-based rollback is only as good at the schema comparison's ability to generate the rollback script. For complex changes that involve data motion, rollback via state will not be possible.

There are three approaches that can be employed to roll back to a previous 'state'. 

1. Roll back to a revision in version control
2. Roll back to a previously deployed package
3. Roll back to a schema snapshot saved prior to deployment

For all three of these options we will use SQL Compare or Schema Compare for Oracle to set the "historic" schema set as the source, and the environment to roll back (eg, production) as the target. Run through the deployment wizard, review the auto-generated rollback script. What you do next depends on whether you are using a state deployment model or a migrations deployment model.

TODO: Screenshot of SQL Compare with loaded data sources.

If using a state deployment model, you can deploy the roll back script immediately if required. However, do not apply the script directly from SQL Compare when a migrations-based approach. A deployment using the SQL Compare script will not register the rollback script execution in the target's migrations log. This means that the rollback would be treated as unwanted drift, which could cause future deployment failures. The correct approach is to save off the SQL Compare script as a new migration script and deploy this using your existing migrations-based deployment process. As database state must be preserved, it can only ever move forwards, which means that a rollback is best regarded as a "roll forward" to a state that happened to exist in the past.

Rolling back to a revision in version control

To roll back to a revision in version control, pull the revision from your VCS to a local folder. Each version control system does this differently.

If you are using Git:

git fetch origin <sha1-of-commit-to-roll-back-to>

The scripts folder that contains the schema model should be used in SQL Compare or Schema Compare as the source of your rollback deployment. If using SQL Change Automation projects, .sqlproj files must be specified as the source.

Roll back to a previously deployed package

Deployment Packages are artifacts that release tools rely on. They are not only useful to perform the deployment, but also as a means to rollback as they contain all data required to perform a deployment to the version represented by the package, which in the case of a rollback would be an earlier version. Most deployment packages will include the schema model (a scripts folder) which can be used as the source of a roll back in SQL Compare as described previously.

Roll back to a schema snapshot saved prior to deployment

There may be reasons to use a schema snapshot as your rollback artifact.

• if your deployment packages don't contain the offline schema model
• if your production instance contains changes "wanted drift" (accepted differences that must not be removed)

For this option to be available, your release script must include a stop to take a schema snapshot of your target (eg, production) prior to the deployment. This artifact can be used as the source of a schema comparison to generate the rollback script.

Testing the state-based rollback process as part of an automated deployment pipeline

Todo

Rolling back using migrations

If you are using a migrations-based approach for your deployments, it might be desirable to adopt a migrations approach for roll backs. This means that for complex changes where there is data motion, custom rollback scripts can be written to undo the changes. Of course if data has been lost as part of a failed deployment, only a backup restore can recover it.

If using SQL Change Automation (SQL Server), see Creating down scripts up-front.

If using Redgate Change Control (Oracle), undo scripts must be authored for each corresponding versioned migration script. This allows the flyway undo command to function in the same way as flyway migrate, except in the opposite direction. See Flyway documentation for more details.

Testing the migrations-based rollback process as part of an automated deployment pipeline

Todo