Why a Backup You Never Restored Is Not a Backup Step by Step

The nightly job runs, the log says done, a file appears in the backup directory every morning. Then the database host fails and the newest usable dump turns out to be six weeks old, or empty. Backups fail quietly, and only a restore tells you the truth. The examples use PostgreSQL 16 and MySQL 8.4; a full exercise is described in a backup restore drill.

How backups fail without noise

ls -l /backup/db/
pg_dump appdb | gzip > /backup/db/appdb.sql.gz; echo "exit: $?"

-rw-r--r-- 1 backup backup   20 Nov  3 02:00 appdb-2026-11-03.sql.gz
-rw-r--r-- 1 backup backup 9.2M Nov  2 02:00 appdb-2026-11-02.sql.gz
exit: 0

The first listing shows a 20-byte file that is just an empty gzip stream: the dump command failed after the shell had already created the file, and the pipeline reported the status of gzip, which succeeded. Without set -o pipefail the failure of pg_dump is hidden, and the same pattern applies to mysqldump | gzip. Other causes: a password that changed, a backup destination that was not mounted so the files went to the local disk, a disk that filled up, a firewall that blocked the database, a key that was lost. See set -euo pipefail.

Make the job fail loudly

#!/usr/bin/env bash
set -euo pipefail

out=/backup/db/appdb-$(date +%F).dump
tmp=$(mktemp "$out.XXXXXX")
trap 'rm -f "$tmp"' EXIT

pg_dump -Fc appdb > "$tmp"
pg_restore --list "$tmp" > /dev/null          # the archive must be readable
(( $(stat -c %s "$tmp") > 1000000 )) || { echo "dump suspiciously small" >&2; exit 1; }
mv "$tmp" "$out"
curl -fsS --max-time 10 https://hc-ping.example.com/abc123 > /dev/null

Writing to a temporary file and renaming means a failed run never leaves a plausible-looking file. pg_restore --list reads the archive's table of contents, which fails on a truncated custom-format dump. A size floor catches the empty case. The final ping tells a dead man's switch that the job succeeded, so the absence of a ping becomes the alert, as in cron dead man's switch pings.

Restore it, somewhere safe

docker run -d --name restore-test -e POSTGRES_PASSWORD=test postgres:16
until docker exec restore-test pg_isready -q; do sleep 1; done
docker exec -i restore-test createdb -U postgres appdb
docker exec -i restore-test pg_restore -U postgres -d appdb < /backup/db/appdb-2026-11-03.dump
docker exec restore-test psql -U postgres -d appdb -Atc "select count(*) from orders"
docker exec restore-test psql -U postgres -d appdb -Atc "select max(created_at) from orders"
docker rm -f restore-test

A throwaway container is isolated from production and needs no setup beyond Docker. Compare the counts with figures you recorded when the backup was made, and check that the newest row is recent. Run the restore on a schedule, at least monthly, and have the script exit non-zero and notify when a check fails. See verifying a database backup is restorable and a throwaway database in Docker.

What else to check

Store the encryption key and the passphrase somewhere that survives the loss of the host, and confirm you can decrypt with it, see compressing and encrypting dumps. Keep one copy off the machine and outside the credentials that could delete it, as in offsite backups with rclone. Record how long a restore takes, because the time grows with the data and the answer to "how long are we down" depends on it.

What a good restore test proves

A restore test answers four questions. Can the backup file be read, decompressed and decrypted with the keys that exist outside the failed host? Does the restore tool accept it, with no errors, on a clean machine with the same major version of the software? Is the data complete and recent, judged by counts and the newest record? And how long did it take, from start to a usable database? The last answer is the one that decides whether the business can wait for a restore, and it is often the surprise: a dump that takes forty minutes to create can take six hours to load because of index builds. Record the time every run, since growth in data makes it grow.

Choose checks that reflect use. A count of rows in a few big tables, the maximum timestamp in an event table, and a checksum of a key column catch truncation and stale data. A query from the application's most important path catches missing indexes, permissions and extensions. Run the application's own integration tests against the restored database when you can, as the most realistic proof.

Automating the test

A test done by hand once is better than none, and a test that runs weekly without anyone remembering is worth much more. Put the steps in a script: fetch the latest backup from the storage the real restore would use, restore into a temporary container or instance, run the checks, record the duration and the results, and report. Send a failure to the same channel as other alerts, and a success to a dashboard or a heartbeat monitor so that a silent failure of the test itself is noticed. Run it on a schedule offset from the backup time, and from a machine with no access to the production database, which also verifies that the backup does not secretly rely on something only the production host has.

The rest of the recovery path

The database is one part of recovery. Check configuration files, secrets, TLS certificates, uploaded files and anything stored outside the database, and record where each lives and whether the backup includes it. Keep a runbook with the exact commands, the order, the people who can run them and the credentials they need, written so that someone who has never done it can follow it at night. Test the runbook by giving it to a colleague and watching where they get stuck. The second sentence of every incident review about a lost backup tends to be "we thought it worked", and a drill makes that sentence unnecessary.

Retention, offsite copies and immutability

Keep several generations: daily for two weeks, weekly for two months, monthly for a year is a common pattern, adjusted for legal needs. Store at least one copy in a different account or location, so a compromised credential or a regional failure does not take both the data and its backups. Ransomware targets backups, so use object lock or a write-only credential for the backup job, and keep the deletion rights with another identity. Verify those protections by trying to delete a recent object with the backup credential and confirming it is refused.

A minimal weekly test script

The following skeleton shows the shape of an automated check. It restores the newest dump into a disposable container, runs two queries, compares them with a recorded floor and exits non-zero when anything is off.

#!/usr/bin/env bash
set -euo pipefail
dump=$(ls -1t /backup/db/*.dump | head -1)
name=restore-$$
trap 'docker rm -f "$name" >/dev/null 2>&1' EXIT
docker run -d --name "$name" -e POSTGRES_PASSWORD=test postgres:16 >/dev/null
until docker exec "$name" pg_isready -q; do sleep 1; done
docker exec "$name" createdb -U postgres appdb
docker exec -i "$name" pg_restore -U postgres -d appdb < "$dump"
rows=$(docker exec "$name" psql -U postgres -d appdb -Atc 'select count(*) from orders')
(( rows >= ${MIN_ORDERS:?} )) || { echo "only $rows orders" >&2; exit 1; }
echo "restore ok: $rows orders from $dump"

Schedule it with a lock and a timeout, and send the result to the place alerts go. The floor value should be raised from time to time, so a restore that loses a week of rows is still caught.

Where this does not apply

Physical backups and point-in-time recovery need their own test, with base backup and log replay, see point-in-time recovery. For managed databases, the provider's snapshots still need a restore test, since permissions and extensions can differ. See the PostgreSQL documentation on SQL dumps and the MySQL backup and recovery chapter.