If for whatever reason a deployment didn't go as expected, recovering to a working state may be necessary.

The only guaranteed method to recover a database if data has been lost is by restoring a backup, so it is absolutely vital that backups are taken regularly and regularly restore-tested.

In circumstances where data doesn't need to be recovered, a roll-back is regarded as a practical way of recovering to a working state.

But is there such a thing as a database roll-back?

Databases change their state over time. And therefore if production has moved on to more recent version, what might seem intuitively feel a roll-back to a previous state is actually a roll-forward to a state that happens to resemble a previous state. This is why there are many heated debates on the topic, many suggesting that roll-back is simply not a "thing" when dealing with databases.

If there have been no data changes at all since the deployment, for example the database is taken off-line during a downtime window, then describing the return to a previous state could be regarded as a true "roll-back". However, the semantics and philosophy is much less important and even less useful than the desire outcome, which is to get back to a working database, which is what we will focus on.

How to recover (if no data recovery is required)

Restoring a backup may be thorough but is time-consuming and over-kill in situations where only the schema state needs to be recovered. It could be that a bug in a stored procedure or function, has slipped passed the testing process, and has been spotted by a customer.

There are two patterns to recover from this situation, to recover to the previous state using a roll-back script or to recover using a schema snapshot as the source of a schema comparison.

Recovering using a roll-back script

It is just as easy to generate a roll-back script. This can be inserted just after the deployment script generation step. A roll-back script is generated the same way as the deployment script, except that the source and target data sources are swapped (see the Schema Compare for Oracle command line example below). If relying on this pattern to roll back, you may choose to add an additional verification step to ensure that the roll back script is tested in the same way as the forward deployment script is tested.

sco.exe /source SOCO_PRODUCTION/demopassword@localhost/XE{SOCO_PRODUCTION} /target State{SOCO_DEV} /scriptfile:Artifacts/rollback_script.sql 

The drawback of using a roll-back script to recover is that it rolls back all changes, may of which may not be problematic.

Recovering using a snapshot

If we've saved a schema snapshot of the production state prior to the deployment, we can use this as the basis of a rollback. Whereas a "forward" deployment is achieved by setting the desired state as the source, and the production database as the target, a reverse deployment would instead set the snapshot as the source.

The advantage of this approach is that the Schema Compare for Oracle UI allows the end user to easily review the changes available to roll back, and selectively pick which changes should be rolled back.

To initiate a "roll back" deployment, set the snapshot file as the source of a comparison using Schema Compare for Oracle, and set the production database as the target.