DEV Community

ULNIT
ULNIT

Posted on

My Backups Passed Every Check for 14 Months. The First Real Restore Said 'database disk image is malformed'

Fourteen months. 428 nightly backups. A green checkmark in my monitoring dashboard every single morning.

I had never restored one.

Not once. Not to a scratch directory, not to a spare Pi, not even to /tmp to poke at it. I had a cron job, a USB SSD, a retry wrapper, an alert if the job didn't run, and a test -s check that confirmed the file existed and wasn't empty.

That is not a backup system. That is a file-copying system with good marketing.

This is the story of the afternoon I finally tested it — what broke, why my verification was worse than nothing, and the 40-line script that replaced 14 months of false confidence.

Why I never tested it

The honest answer is embarrassing: I was afraid of what I'd find, and I had a rationalization ready.

"Restoring takes downtime." It didn't. It's a Pi and a spare SSD; I could restore to a temp directory while everything kept running.

"The snapshots are verified." They were verified for existence. I had conflated "the job exited 0" with "the data is recoverable." Those are the same thing only if your backup method is correct, and mine wasn't.

And here's the part that actually stings: I write about agent reliability. I have written about lock files, retry storms, and log rotation. I had a mental category called "things you must actually test" and backups somehow lived outside it, in the category called "things that are supposed to just work."

The drill

Nothing exotic. I picked the oldest snapshot in the repo, restored it into /mnt/restore-test/, then tried to open it the way my agent opens it in production:

restic restore latest --target /mnt/restore-test --include /srv/agent/data
sqlite3 /mnt/restore-test/srv/agent/data/app.db "PRAGMA integrity_check;"
Enter fullscreen mode Exit fullscreen mode

The restore itself was quick — about four minutes for 2.1 GB. Then SQLite answered in under a second:

database disk image is malformed
Enter fullscreen mode Exit fullscreen mode

Not "file not found." Not "permission denied." The bytes were there. They just weren't a database.

Failure 1: I was copying a live database with cp

Here's the backup line I'd been running for 428 nights:

cp /srv/agent/data/app.db "$DEST/app.db"
Enter fullscreen mode Exit fullscreen mode

If you've spent time with SQLite, you already know. In WAL mode, the database file alone is not the database. The truth is spread across app.db, app.db-wal, and app.db-shm. cp grabs a page-level snapshot with no coordination with writers, so you can capture a file mid-transaction — pages that reference a WAL frame that you didn't copy, or a header whose checksum no longer matches its contents.

The result is a file with the right size, the right extension, and the wrong soul. It only fails when something actually tries to read all of it. Which is to say: it only fails when you need it.

The correct primitive has existed the whole time:

sqlite3 /srv/agent/data/app.db ".backup '$DEST/app.db'"
Enter fullscreen mode Exit fullscreen mode

.backup uses SQLite's online backup API. It holds the right locks, it's consistent while writers continue, and it produces a single self-contained file. Or, if you want the dump form:

sqlite3 /srv/agent/data/app.db "VACUUM INTO '$DEST/app.db'"
Enter fullscreen mode Exit fullscreen mode

Both are one line. I chose the wrong one for over a year because it was the first thing I typed and it never complained.

Failure 2: my "verification" tested the wrong property

This is the part I'd flag if I were reviewing someone else's code, and I still shipped it:

if [ -s "$DEST/app.db" ]; then
  echo "backup ok"
fi
Enter fullscreen mode Exit fullscreen mode

-s means "exists and is non-empty." A truncated, torn, half-copied database passes that test with room to spare. My 14 months of green checkmarks were 14 months of the operating system telling me a file had bytes in it.

The check I should have run, on the copy, before calling it a backup:

result=$(sqlite3 "$DEST/app.db" "PRAGMA quick_check;")
[ "$result" = "ok" ] || { echo "CORRUPT: $DEST/app.db"; exit 1; }
Enter fullscreen mode Exit fullscreen mode

quick_check skips the expensive index cross-verification and catches structural corruption in milliseconds on a file this size. integrity_check is stricter and worth running on the restore side, not every night.

A backup you haven't read is a hypothesis. quick_check is the cheapest way to turn it into a fact.

Failure 3: the near-miss that would have been worse than the original bug

While setting up the drill I almost did this:

restic restore latest --target /srv/agent/data   # NO. Do not do this.
Enter fullscreen mode Exit fullscreen mode

restic restore overwrites paths under the target. If my latest snapshot had been one of the torn ones, I'd have replaced a working production database with a corrupt one — during a "test." I caught it because I'd typed the command with --target / first and my stomach dropped before I hit enter.

The rule I now follow: restores never touch a live path. Restore to a scratch directory, verify there, and only then, deliberately, as a separate documented step, swap anything.

There was a second near-miss in the same session. My first instinct for cleanup was rsync --delete to "make the scratch dir match the snapshot." On a path where I'd fat-fingered one directory level, that's a data-loss command wearing a maintenance costume. I deleted the idea and used rm -rf on a path I'd typed out in full and read twice.

What I do now

The whole system is one script and one rule.

The rule: the backup job is not done when the file is written. It's done when the file has been read back and validated.

The script, roughly:

set -euo pipefail
DEST=/mnt/backup/staging
LATEST=/srv/agent/data/app.db

mkdir -p "$DEST"
sqlite3 "$LATEST" ".backup '$DEST/app.db'"

check=$(sqlite3 "$DEST/app.db" "PRAGMA quick_check;")
[ "$check" = "ok" ] || { echo "FATAL: backup failed quick_check" >&2; exit 1; }

sha256sum "$DEST/app.db" > "$DEST/app.db.sha256"
restic backup "$DEST" --tag agent-db
restic forget --keep-daily 14 --keep-weekly 8 --prune
Enter fullscreen mode Exit fullscreen mode

Three things changed, and none of them are clever:

  1. Online backup API instead of cp. Consistency, not luck.
  2. quick_check before the file counts as a backup. The job now fails loudly instead of shipping a plausible-looking corpse.
  3. A checksum manifest, so a later restore can prove it got the same bytes the backup job validated — not just a file with the same name.

And once a month, on the first Sunday, a calendar entry fires: restore the newest snapshot to /mnt/restore-test, run integrity_check, run one real query that my agent would run, and delete the scratch directory. Fifteen minutes. I've done it three times. It has caught one more problem: a snapshot that was structurally fine but six days stale, because restic forget --prune had run out of disk mid-prune and left the repo in a state where new snapshots silently failed the tag filter.

The lesson I keep relearning

I had monitoring for the absence of backups and none for the quality of them. That's the same class of mistake as an agent that logs "success" without checking the response body, or a retry loop that counts attempts instead of outcomes. Green means "the code ran." It has never meant "the thing worked."

Fourteen months of backups, and my real recovery point was zero. The restore drill didn't just find a corrupt file — it told me which of my green checkmarks were lies. Turns out most of them were test -s.

If you take one thing from this: pick your oldest snapshot this week, restore it somewhere harmless, and try to actually use it. You'll either get fifteen minutes of boredom (the good outcome) or you'll find out now instead of at 2 AM.

The verification habits and the agent ops playbooks I use — backup validation, restore drills, the checks that catch "exit 0 but wrong" — are written up in The Solo Operator's AI Agent Playbook — code LAUNCH90 at checkout makes it $1.90. If it doesn't save you 5 hours in week one, reply to the receipt for a refund.

Top comments (0)