Database backup verification: a practical checklist
"Verify your backups" can mean anything from "the job exited with code 0" to "we restored it and ran the application against it". Each level catches failures the previous one misses. Here's the ladder, what each step catches, and how to automate it for PostgreSQL and MySQL.
Level 1: the job ran
The backup job finished and reported success. This is what most monitoring stops at.
Catches: jobs that crash or never start. Misses: almost everything else. A job can "succeed" while writing an empty or partial file.
Do it: alert when the job fails and when it doesn't run at all (a dead man's switch: the job checks in on success, and silence triggers an alert).
Level 2: the file is where you think, and it's new
Check the backup in its final storage location, not on the machine that made it: it exists, it's from today, and its size is in line with recent backups.
Catches: failed uploads, lifecycle rules that deleted files, a job writing to the wrong bucket, sudden size drops (often a sign of missing data).
Do it: list the bucket from a separate, read-only account; compare size with the median of the last week.
Level 3: the file is intact
The file decompresses and decrypts, and its checksum matches what the backup job recorded.
Catches: truncated uploads, corrupted storage, lost or rotated encryption keys (a classic: backups encrypted with a key nobody can find anymore).
Do it: gzip -t / zstd -t, decrypt with the production key path, compare SHA-256. For PostgreSQL custom-format dumps, pg_restore --list backup.dump reads the table of contents without restoring.
Level 4: it restores without errors
Restore into a fresh, isolated database server running the same major version as production. Treat any error as a failure.
Catches: dumps that are internally inconsistent, missing roles or extensions, version incompatibilities, and anything Levels 1–3 can't see.
Do it: PostgreSQL: pg_restore --exit-on-error or psql -v ON_ERROR_STOP=1. MySQL: pipe the dump into mysql and check the exit code. Record the time it takes: that's your real recovery time.
Level 5: the data is physically sound
Read everything back. A restore can finish while some data is unreadable, and indexes can be inconsistent with the tables.
Catches: corrupted pages and values, broken indexes. Especially relevant for physical backups (file-level copies, snapshots).
Do it: PostgreSQL: read every row of every table and run amcheck (bt_index_check with heapallindexed). MySQL: CHECK TABLE or mysqlcheck --check --all-databases.
Level 6: the data is complete and current
This is the level that catches the quiet disasters: the backup restores perfectly, but it's from a replica that stopped replicating two weeks ago, or a table has a fraction of its usual rows.
Catches: stale replicas, partial dumps of the wrong database, mass deletions that happened before the backup, dropped tables and columns.
Do it: after each restore, record the row count per table and the newest timestamp in key tables (max(created_at) on orders, users, events). Compare with previous runs: alert when a table drops well below its usual size or the newest record is much older than the backup.
Level 7: the application works on it
Point a copy of the application (or its test suite, or a few critical queries) at the restored database.
Catches: problems only the application notices: missing reference data, broken migrations, configuration stored in the database.
Do it: a handful of SQL assertions that encode business facts ("there is at least one order from the last 24 hours", "every active customer has a plan") gets you most of the value cheaply. Full application smoke tests belong in periodic disaster recovery exercises.
How far up the ladder should you go?
| Situation | Reasonable target |
|---|---|
| Hobby project | Levels 1–3, plus an occasional manual restore |
| Production database for paying customers | Levels 1–6, automated, daily or weekly |
| Preparing for SOC 2, ISO 27001 or NIS2 | Levels 1–6 automated with records kept, plus a yearly Level 7 exercise (see what auditors expect) |
Levels 4–6 sound like the most work but automate well: a scheduled job that restores into a throwaway container and runs the checks. Our guide How to test PostgreSQL backup restores automatically has a tested script covering Levels 3–5 and the measurements for Level 6.