Course 3 · Module 3 — Rollback & recovery

The release went out clean, passed staging, sailed through prod — and an hour later the error rate’s climbing and someone says the word nobody wants to hear: roll it back. Now you’re hunting for the undo script you hope someone wrote, reading an ALTER in reverse in your head, praying the down-migration actually mirrors the up. That’s the moment rollback stops being a plan and becomes a gamble.

Here’s the better way: there’s no undo script to find, because rolling back is just deploying the version that worked.

The prior package is the rollback plan

You already know how SchemaQuench deploys forward — it reads the desired state from your package, diffs it against the live database, and computes the strikes to close the gap. Rollback is the exact same operation pointed at an older package. Check out last release’s tag, quench it, and SchemaQuench computes the delta that brings the database back to that state. No separate down-migration. No reverse-engineering. The same hammer that shaped the change reshapes it back.

That’s the whole payoff of state-based deployment. With migration scripts you maintain two of everything — the change and its undo — and you pray they stay in sync. With SchemaSmith there’s one artifact per release, and any release can be the target. Going backward and going forward are the same move; only the package version changes.

What reverts on its own, and what doesn’t

Most of a rollback is automatic. Deploy the prior package and SchemaQuench reverts the structure to match it:

Tables and columns added in the newer release drop; columns it removed come back.
Indexes, constraints, and foreign keys revert to their prior definitions.
Stored procedures, functions, and views restore to the old version — because your package carries each object’s full definition, SchemaQuench just reapplies it.

That last one’s the quiet win. No hand-maintained rollback script for your procs and views — the package is their history, and the prior version is right there.

But structure isn’t data, and this is where you slow down. When a rollback drops a column or a table, the data in it is gone. If the newer release added a column your app’s been writing to, rolling back drops that column — and every value in it. SchemaSmith reverts the schema; you decide what happens to the data. When a drop would destroy something you need, you write a data-preservation script to copy it out first. The declarative model handles the shape. You handle the contents when the operation is destructive.

WhatIf first — on rollback too

So how do you know what a rollback will destroy before it destroys it? The same gate you use going forward. Run WhatIf against the live database with the old package, and SchemaQuench prints the full reverting delta — every drop, every revert — and applies nothing.

In this module’s lab you ship v2 (a new Promotion table and a Customer.PromotionId foreign key), then WhatIf the prior v1 against that database. The preview is blunt about it:

Dropping foreign Key [dbo].[Customer].FK_Customer_Promotion
ALTER TABLE [dbo].[Customer] DROP CONSTRAINT IF EXISTS [FK_Customer_Promotion];
Dropping columns from [dbo].[Customer] ([PromotionId])
ALTER TABLE [dbo].[Customer] DROP COLUMN [PromotionId];

That DROP COLUMN is the line you read twice. In the lab the column’s empty, so there’s nothing to lose — but in production that’s the moment to ask: is there data in PromotionId I need? If yes, preserve it before you quench. WhatIf is the dry strike for rollbacks: swing in the air, see exactly where the hammer lands, then decide.

Make the drop reversible — the recyclebin

WhatIf shows you the drop. A data-preservation script saves what’s in it. But for a dropped table there’s a third move, and it’s the cleanest one: don’t destroy it at all — set it aside where you can pull it back.

That’s a recyclebin, and you wire it into the package once. Install a pair of hooks — CustomTableDrop and CustomTableRestore — and SchemaQuench stops throwing removed tables on the fire. When a rollback would drop a table, the drop hook catches it and moves it aside instead — rows and all — into a holding area, and logs what it stashed. The table’s gone from your schema. The data isn’t gone anywhere.

Then comes the quiet part. Roll forward again — redeploy the release that has the table — and the restore hook fires before SchemaQuench can build an empty one. It finds the set-aside copy and brings it back, data intact. No backup script, no export-and-reload, no held breath. The piece cooled in the bin, and when you needed it again you pulled it straight back out.

It doesn’t sit there forever. Each recycled table carries a retention window — a count of days before it’s fair game for the melt. A cleanup procedure sweeps the bin and scraps anything past its window, so the holding area never turns into a junk drawer. Set the window to match how long you’d realistically want the option to undo: long enough to change your mind, short enough to stay tidy.

In the lab you watch the whole arc. Seed a row into Promotion, roll back — auto-drop is on by default, so the table lands in the recyclebin with your row riding along instead of being destroyed. Roll forward — it’s back, same row. Shorten the retention, run the cleanup, and watch it go to the melt. Soft-drop, auto-restore, purge.

One thing the recyclebin doesn’t do is think for you. It’s the scrap bin by the anvil, not a reason to skip the dry strike. You still preview every drop, you still decide what’s destructive — the recyclebin just means a dropped table is a call you can take back, not a crater you can’t.

One note on the metal itself

Rollback semantics are identical on all three engines — deploy the prior package, done. But the engines differ in how DDL behaves inside a deploy, and it’s worth knowing. PostgreSQL has fully transactional DDL, so a failed multi-statement deploy unwinds as a unit. SQL Server runs most DDL transactionally, with a few exceptions. MySQL doesn’t — every DDL statement auto-commits, so the engine won’t unwind a half-applied deploy for you. On MySQL especially, recovery is re-quenching the prior version, so back up before a big rollback and test the path first.

Check yourself: How do you roll back a schema release with SchemaSmith?

Deploy the prior release’s package — SchemaQuench computes the delta to bring the database back to that state. No rollback scripts; just WhatIf first, then quench the old version.

Think of it this way. A migration-based rollback is a second piece of metal you forged in the dark and stashed away, hoping it still fits when the day comes. SchemaSmith doesn’t make you keep a spare — the version that worked is its own mold. When you need to go back, you set the old jig on the anvil, run the dry strike to see every blow, preserve anything the fire would consume, and quench. Same tool, same motion, pointed the other way.

Want the deeper treatment of rollback strategy — when to preserve data, how to tag releases for easy retrieval, how the auto-drop posture changes what a rollback touches? Read the Database rollback strategies guide on the SchemaSmith site, and the end-user guide’s Rollback and Recovery chapter.

Got a rollback you’re nervous about — which drops are destructive, what to preserve, how to rehearse it in staging first? Email me at forgebarrett@schemasmith.com — I read every one.

Next up: Course 3 · Module 4 — Multi-environment promotion & zero-downtime, where the same package threads dev to staging to prod without ever taking the lights down.

Until then, may your every retreat be as sure-footed as your advance, and the version that worked always be a quench away.

— Forge

Mark this lesson complete