Contents
The rollback plan is the easiest line to write in a deployment slide and the least tested in practice. The sentence "we will roll it back if something goes wrong" is usually a wish rather than a plan. On top of that, how rollback actually works differs sharply between database engines.
The Common Illusion: We Have a Backup
A backup is a disaster recovery plan, not a rollback plan. The difference is time and scope. Restoring a large database takes hours, and the system is down for all of them. Worse, a restore also erases the correct transactions that happened after the faulty one. Losing the real orders taken in that hour because of one bad update turns the remedy into a new incident.
A real rollback plan therefore has to be a script of counter operations: one that deletes what was inserted, restores what was deleted and returns a changed structure to its previous shape.
Three Engines, Three Behaviours
| Topic | SQL Server | PostgreSQL | Oracle |
|---|---|---|---|
| Can DDL be rolled back in a transaction? | In most cases yes | Yes, strong support | No, an implicit commit occurs |
| Effect of a half finished script | No effect if the transaction rolls back | No effect if the transaction rolls back | Completed DDL is permanent |
| Design of the rollback script | Counter operations | Counter operations | Counter operations, necessarily idempotent |
The Oracle difference changes the entire rollback strategy. If a script contains five DDL statements and the third fails, the first two are already permanent. There is no all or nothing guarantee. The rollback script must therefore be able to run without knowing which steps actually happened.
Why Rollback Must Be Idempotent
Idempotent means running the same script twice produces the same result as running it once. In a rollback script this is a requirement rather than a luxury, because when you run it you are not certain where the system stands. Perhaps half the change was applied, perhaps none of it, perhaps someone already undid part of it by hand.
In practice this means every step checks existence first: look before you drop, check before you create. A rollback script that stops with an object not found error becomes useless at the worst possible moment.
Rolling Back DML: The Hard Part
Undoing a structural change is relatively easy: you drop the column you added. Undoing a data change requires knowing the previous value. Copying the affected rows into a backup table before the update is the most reliable method. It makes the script a little longer, but it is the only thing that makes reversal possible.
Careful with deletions
Restoring deleted rows means restoring relationships too, not just data. If identity values are generated automatically, writing them back with the same values takes extra effort and is sometimes impossible. Deletions should therefore be treated as higher risk than updates.
A Realistic Rollback Plan
A realistic plan has five properties:
- It is produced together with the change. A rollback written later is usually never written at all.
- It is part of the request. A script sitting in a separate file cannot be found when it is needed.
- It is idempotent. It runs even from a half applied state.
- It has been tried. A rollback script that was never executed is an assumption, not a plan.
- It can be made mandatory. On critical environments a change without a rollback script should not run at all.
All five can be done by hand, but in practice that does not survive contact with a busy week. Rollback generation should be automated, the generated script should be reviewed by a person and its presence should be enforced by the system.
Frequently Asked Questions
If we have a backup, why do we need a rollback plan?
A backup is a recovery tool, a rollback plan is a change tool. Restoring from a backup usually takes the whole database back to that moment, which also erases the other work that happened in between. When you want to undo a single change, a backup is usually far too broad and far too slow.
Why does rollback behave differently on Oracle?
On Oracle, DDL statements produce an implicit commit: a statement that alters a table becomes permanent the moment it runs and the transaction cannot be rolled back. On SQL Server and PostgreSQL, DDL can usually take part in the transaction. That difference means the rollback plan is the only safety net after the fact on Oracle.
Why must the rollback script be idempotent?
Because rollback usually runs on top of a half finished state: part of the change was applied and part was not. A script that tries to recreate an object that already exists stops at the first error and leaves the state even more confused. An idempotent script reaches the same result no matter where it starts.
See rollback generation live
We will demonstrate generation on your own DDL and DML scripts, along with Oracle implicit commit behaviour and the mandatory rollback setting.
Book a Demo →