Building an Automated Backup Verification Workflow (Because "We Have Backups" Isn't the Same as "Backups Work")

The nightly backup job had a green checkmark every single morning for over a year — the job ran, produced a file, uploaded it to storage, and reported success, exactly as designed. What "success" actually meant, it turned out, was narrower than anyone had assumed: the job succeeded at producing and uploading a file. Whether that file could actually be restored into a working database was never checked automatically, by that job or any other, until an actual restore was needed and three of the previous thirty nightly backups turned out to be silently corrupted.

What "the backup succeeded" was actually verifying, and what it wasn't

The original backup job's success criteria were narrow by design, though nobody had framed it that way explicitly at the time: the database dump command exited without an error code, the resulting file was non-empty, and the upload to cold storage completed. Every one of those checks is real and worth having — and none of them verify that the file's actual contents are a valid, restorable database dump, which is a meaningfully different and stronger claim than "a file of nonzero size was produced and uploaded without an error."

How the corruption actually happened

The root cause, found afterward, was a disk space issue on the database server that intermittently caused the dump process to be killed partway through by the operating system's out-of-memory handling — not consistently, and not in a way that produced a nonzero exit code, because the killing signal arrived after the dump tool had already written a partial, truncated file and, in the specific failure pattern that occurred, the tool's own exit handling didn't always propagate that interruption as an error the calling script checked for. The result: a real, uploaded, correctly-timestamped file, that also happened to be an incomplete, unrestorable database dump — invisible to every check the original job actually performed.

Discovering it the hard way

The discovery happened during an unrelated infrastructure migration that required actually restoring a recent backup into a staging environment as part of the migration process — the first time in over a year that any backup had actually been restored rather than just produced and stored. The most recent backup restored cleanly. Working backward to find a slightly older backup for a specific comparison, two of the next several attempted restores failed outright with a corruption error, and a third restored but was missing several hours of data that should have been present, a more subtle and arguably more dangerous failure than an outright restore error, since it wouldn't have been immediately obvious during a real disaster-recovery restore performed under actual time pressure.

The verification workflow built afterward

# Simplified version of the nightly verification step, added after the backup job
1. Backup job runs and uploads as before (unchanged)
2. Verification job downloads the just-created backup to a scratch environment
3. Verification job restores it into a throwaway database instance
4. Verification job runs a set of integrity checks against the restored data:
- Row counts for critical tables, compared against a expected-range check
- A handful of specific, known records checked for exact expected values
- Foreign key constraint checks pass with no orphaned references
5. On any failure: alert immediately, flag that specific backup as unverified,
and re-run the original backup job once before escalating further
6. On success: mark the backup as verified in a simple tracking table

The critical design decision was step 3: the verification has to actually restore the file into a real, working database, not just check that the file is well-formed at a syntax level. A file that parses as valid SQL but is missing several hours of transactions, as happened in the incident above, would pass a syntax-only check and fail exactly the check that actually matters — whether the data restored from it is complete and correct.

What the row-count and known-record checks actually catch

Row counts alone catch gross failures — a table with a million rows the night before and eight hundred rows in the freshly restored backup is an obvious, unambiguous signal something is wrong, without needing to know exactly what's missing. The known-record checks catch subtler problems: a small set of specific records, chosen because they're expected to be stable and present in every backup, get checked for their exact expected values after every restore, which catches the kind of partial, silent data loss that a row-count check alone can miss if the truncation happens to land in a way that still produces a plausible-looking total count. Neither check alone is sufficient; together, they cover most of the realistic failure modes actually seen so far.

The cost of running this every night, and why it's worth it anyway

The honest tradeoff: the verification workflow adds real infrastructure cost, since it requires spinning up a throwaway database instance and running a full restore every single night, not just occasionally — for a database of meaningful size, that's a non-trivial, recurring compute cost, not a one-time setup expense. It's been kept anyway, because the alternative cost — discovering a corrupted backup only during an actual disaster recovery, under real time pressure, with a business genuinely depending on that specific restore succeeding — is categorically worse than the ongoing infrastructure cost of verifying every single night. A monthly-only verification schedule was considered as a cheaper middle ground and rejected specifically because it would have caught this incident's corruption a full three weeks later than nightly verification did, which is three weeks of accumulating unverified, potentially unusable backups.

What this connects to about automation reliability more broadly

This sits in the same category as the failure pattern described in why your automation keeps silently failing (and how to catch it) — an automation reporting success is only as meaningful as what "success" was actually defined to check, and a narrow definition of success (the job ran without an error) can coexist for a long time with the automation quietly not accomplishing its actual real-world purpose (having a genuinely restorable backup). The fix in both cases is the same underlying discipline: define success in terms of the actual outcome that matters, not just in terms of the process completing without an error, and build the verification to check that outcome directly rather than trusting a proxy for it.

Keeping the verification workflow itself from silently rotting

A verification workflow that's never actually tested against a genuinely broken backup is itself vulnerable to the exact same silent-failure pattern it exists to catch — so once a quarter, a deliberately corrupted test backup file gets fed through the verification pipeline specifically to confirm it still correctly flags a bad backup as bad, rather than assuming the verification logic itself remains correct indefinitely without ever checking. This mirrors the same principle covered in version-controlling your automations so a bad change doesn't take down production — an automation that guards against failure needs its own periodic check that it still actually works, not just an assumption that it does because it worked when it was first built.

What changed about how "we have backups" gets said now

The specific, concrete change in how this gets talked about internally: "we have backups" is no longer treated as a complete statement on its own. The actual claim that matters is "we have backups, and we verified last night's specific backup restores correctly" — a meaningfully stronger and more precise statement, and one that's now genuinely true every single day, rather than an assumption that happened to hold for over a year until the one day it didn't, and nobody would have known until it mattered most.