Database Rollback Strategies
Rollback plans turn database migration failures from crises into routine procedures. Know your exit before you move forward.
By the SchemaSmith Team · Last reviewed
Rollback plans turn database migration failures from crises into routine procedures. Know your exit before you move forward.
By the SchemaSmith Team · Last reviewed
Every production database migration needs a rollback plan. The two main strategies are "fix forward" (deploy a corrective change) and "roll back" (revert to the previous state). Schema rollback is often straightforward because structure can be restored automatically. Data rollback requires planning because once data is transformed or deleted, it cannot be recovered without an explicit preservation script or backup. The best rollback strategy depends on whether your migration changed only schema, moved data, or both.
Production migrations fail more often than teams expect. Constraint violations, lock timeouts, data conversion errors, and unexpected database state all cause deployments to not go as planned. Without a plan, a failed migration becomes an extended outage while the team figures out what to do next. Someone opens a query window, someone else checks the backup schedule, and a third person starts writing a reversal script from scratch. Meanwhile, the application is down or behaving unpredictably.
The time to plan your rollback is before you deploy, not during a 2 AM incident. A rollback plan answers one question in advance: "If this migration fails, what exactly do we do next?" Having that answer ready turns a potential crisis into a routine procedure.
There are two fundamental responses to a failed migration. Each has trade-offs that depend on the nature of the change.
If the change was additive (adding things), fix forward. If the change was destructive (removing or renaming things), you need a rollback plan. Danilo Sato's Parallel Change writeup on Martin Fowler's bliki covers why additive-then-cleanup is the lower-risk shape for evolving live systems.
This is the critical distinction that most rollback discussions overlook. Pramod Sadalage and Martin Fowler make the same separation in their writing on Evolutionary Database Design: schema is reversible by definition; data transformations are not.
Most tools advertise "rollback support" but only handle schema rollback. Data rollback almost always requires manual planning. If your migration merges two columns into one, drops a table after migrating its data elsewhere, or changes a column's data type in a lossy way, no tool can automatically reverse that without a backup of the original data.
Different situations call for different rollback methods. Here is how the common approaches compare.
| Approach | Speed | Data Safety | Complexity | Best For |
|---|---|---|---|---|
| State-based revert | Fast | Schema only (data may need manual handling) | Low | Schema-only changes |
| Down migration scripts | Medium | Depends on script quality | High (must write and test) | Changelog-based teams |
| Database snapshot/restore | Slow | Full data safety | Low (but requires storage) | Destructive changes |
| Point-in-time recovery | Slow | Full data safety | Medium | Catastrophic failures |
| Blue-green switch | Fast | Full data safety | High (infrastructure cost) | High-availability requirements |
Vendor references: SQL Server database snapshots and point-in-time restore under the full recovery model; PostgreSQL backup and restore and continuous archiving and PITR; MySQL point-in-time (incremental) recovery.
Before every production migration, answer these six questions.
Writing these answers down before deployment takes five minutes. Figuring them out during an outage takes much longer, and the answers are usually worse.
Rolling back means deploying the prior release's package. Because deployment is state-based — SchemaQuench compares the declared definition against what is live and generates only the difference — reverting is a matter of pointing it at an earlier definition rather than composing a reversal script. Most rollbacks need nothing more. The ones that do are the ones that touch data, and this procedure is ordered to surface those before anything runs.
Check out the tagged release from source control, or retrieve the schema package artifact kept from the earlier deployment. Tagging every release is what turns this step into a lookup instead of an archaeology exercise.
WhatIf mode reports what the rollback would change and applies nothing:
SmithySettings_WhatIfONLY=true SchemaQuenchWhatIf is a human-facing preview — for reviewing the scope of a change, rehearsing a correction, or debugging a deployment before running it for real — so its value depends on someone actually reading the generated SQL. Two things to look for: tables that exist now but are absent from the older package, and columns being dropped.
DropTablesRemovedFromProduct defaults to true, so rolling back to an earlier package removes the tables the newer release added. That converges cleanly and destroys their data. Two postures are safe:
false, so the rollback leaves those tables in place until you are confident the revert is permanent, then clean them up explicitly.SchemaSmith.CustomTableDrop / SchemaSmith.CustomTableRestore recyclebin hooks, which move a removed table to a recoverable holding area instead of destroying it.Neither posture covers columns. A dropped column is not caught by either mechanism, so preserve its data yourself — with a migration script — before a rollback that drops it. PreventDrop: true protects one specific table and is sticky, persisted in the database so the table is skipped even after it leaves the package entirely, but it protects tables, not columns.
SchemaQuench records every completed step and migration script to disk as it runs. Re-running with --ResumeQuench skips what already finished and picks up at the first incomplete step; without that switch the resume logic is off and every step executes again. That is recovery — getting a stalled deployment over the line — not undo. A rollback is the separate, deliberate act of deploying an earlier package. Take a backup before a significant one, especially on MySQL and MariaDB, where the engine cannot unwind partial DDL on its own.
Deploy the previous version of the schema package with SchemaQuench. It computes the delta from the current state back to that earlier version and makes only those changes. Deployments are idempotent, so the database converges on the declared state either way.
Not by itself. Rolling back reverts structure; schema state alone cannot recreate lost rows, and a dropped column's data is gone for good. A dropped table is recoverable only if you have installed the optional recyclebin hooks, which soft-drop it for a retention window instead of destroying it. If a rollback would remove data you need, plan for it separately: take a backup before the change, or use a migration script that moves the data somewhere safe before the original is dropped.
Check DropTablesRemovedFromProduct. It defaults to true, so rolling back to a package that no longer declares a table will drop it. In production many teams set it to false, leaving such tables in place until the rollback is confirmed permanent.
Practice rolling back by re-deploying the prior release's package, and add a data-preservation script when a change would otherwise drop data you need.
Start the recovery labEverything from here is organized by database. Choose yours below and keep going.
Free schema-as-code for SQL Server, PostgreSQL, MySQL, and MariaDB.
The source is on GitHub for anyone to read.
Get Started on GitHub