How to test your database backups

TL;DR

• A backup you have never restored is a hope, not a backup. Most restore failures are discovered during a real outage, which is the most expensive possible time.
• Test the whole path: restore to a fresh instance, roll forward through WAL or binlogs, verify row counts and constraints, and run the application against it.
• Measure two numbers during the drill: how much data you would have lost (RPO) and how long the restore took end to end (RTO). Compare them to what the business can tolerate.
• Automate a weekly restore-and-verify job; keep a quarterly manual drill where a human follows the runbook cold.
• The common failure causes are mundane: silently failing backup jobs, missing WAL archives, expired credentials, retention misconfiguration, and restores that were never tested at production size.

Why “we have backups” is not “we can restore”

Backup jobs fail quietly. The cron job that has been exiting non-zero for five months, the dump that is 40 bytes of error message, the retention policy that deletes the full backup your incremental chain depends on, the S3 bucket the new account cannot read. None of these page anyone, because the backup side looks green: files appear, dashboards stay quiet.

The only test that counts is the one that answers the real question: starting from nothing but the backups, can we get a working database, with acceptable data loss, in acceptable time, using only the documented procedure? Everything below is that sentence turned into a drill.

Step 1: Define what “restored” means first

Write down, before the drill:

• RPO (recovery point objective): the maximum data loss the business tolerates. If the answer is “5 minutes”, nightly dumps alone cannot meet it; you need continuous WAL or binlog archiving for point-in-time recovery.
• RTO (recovery time objective): the maximum acceptable downtime. This includes noticing, deciding, provisioning, restoring, verifying, and cutting over, not just the restore command’s runtime.
• Scope: which databases, and what “working” means: schema present, data intact, users and permissions in place, application connects and functions.

The drill’s job is to measure your actual RPO and RTO and hold them up against these numbers. A 6-hour restore against a 1-hour RTO is a finding, and a cheap one, because you found it on a Tuesday afternoon instead of during the incident.

Step 2: Run the restore drill

1. Provision a scratch target. A fresh instance or container, same major version, sized realistically. Never restore over anything that matters, and never test on the production host.
2. Fetch the backup the way a disaster would. Pull it from the offsite location using only the credentials in the runbook. If the backup lives only on the same disk, account, or datacenter as the database, that is finding number one.
3. Restore the base backup.
   • PostgreSQL: pg_restore -d appdb --jobs=4 backup.dump for logical dumps, or restore the base backup and recover for physical (pgBackRest, wal-g backup-fetch).
   • MySQL: load the dump, or xtrabackup --prepare then --copy-back for physical backups.
   • Managed services: actually click (or script) the point-in-time restore and see what comes out the other side.
4. Roll forward to a point in time. If you archive WAL or binlogs, practice recovering to a specific timestamp (recovery_target_time on Postgres, replaying binlogs with mysqlbinlog --stop-datetime on MySQL). This is the skill that saves you after a bad DELETE, and it is the step most teams have never once executed.
5. Time everything. Wall clock from “decision to restore” to “application works”. That number is your real RTO.

Step 3: Verify integrity, not just existence

A restore that completes is not a restore that worked. Check in layers:

Layer	Check
Database opens	Server starts clean, no recovery errors in the log
Structure	Schema objects, indexes, views, functions, users, and grants all present
Volume	Row counts on the largest and most critical tables, compared to production or to counts recorded at backup time
Consistency	Constraint and checksum verification: pg_amcheck / CHECK TABLE; foreign keys spot-checked
Freshness	The most recent rows match the expected recovery point (an orders table’s MAX(created_at) tells you your real RPO)
Application	Point a staging app at the restored database and run a smoke test: log in, read a record, write a record

The application smoke test catches the whole category that database-level checks miss: missing extensions, collation mismatches, absent app users, connection parameters that only exist in production.

Step 4: Automate the periodic test

Manual drills go stale between quarters. Automate the core loop:

1. Nightly or weekly job: spin up a scratch instance, restore the latest backup, run the verification queries (row counts, checksums, MAX(created_at) freshness), tear it down.
2. Report to a channel a human reads, and page on failure or on silence. A verification job that stops running must be as loud as one that fails; silent-death is exactly how backup jobs rot.
3. Record the restore duration each run, so RTO drift (the database grew, the restore no longer fits the window) shows up as a trend, not a surprise.

Keep the quarterly manual drill anyway, with one rule: the person running it follows the written runbook and nothing else, ideally someone who did not write it. Every question they have to ask a colleague is a gap in the runbook, found for free.

Common reasons restores fail

• The backup job had been failing or producing empty files for months, and nothing alerted.
• Incremental or WAL chains with a hole: the full backup expired, or archiving broke, so point-in-time recovery stops at a cliff.
• Encryption or credential problems: nobody can find the key, or the restore role lacks bucket access.
• Version and environment mismatch: backup from 14, restore target is 16, or a required extension is missing.
• Retention shorter than assumed: the incident is discovered on day 9, retention is 7 days.
• It works on the 2 GB staging database and takes 14 hours on the 800 GB production one, because it was never tested at real size.
• The one person who knows the procedure is on a plane. The runbook is the fix, and the drill is what keeps the runbook honest.

When to call for help

If a drill just told you the backups do not restore, that finding is urgent but fixable calmly. If you are reading this mid-incident with a restore that will not come up, stop experimenting on your last good copies. Erzon handles production database recovery from triage, first response within one business hour and a flat quote before work begins, in your own infrastructure with least-privilege access, and every incident closes with a written root-cause report and a prevention list, which in backup incidents usually becomes the automated restore test you will wish you had had this week.