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. 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?

Neither rolling back nor rolling foward 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 except 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.

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 (eg, 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.

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

Rollback support in Redgate Change Automation

Redgate Change Automation provides functionality that can support your efforts to prepare for the scenario in which a rollback is needed:

• Rolling forward
• Rolling back