A Backup You Have Never Restored Is a Hope, Not a Backup

Your backup job has exited 0 every night for a year, and that tells you almost nothing. It tells you a process ran and did not crash. It does not tell you the archive contains the right files, that the database inside it is consistent, or that you still have the key to open it.

The fix is boring and takes about an hour: a restore drill. You restore into a scratch location, check the result against something real, and write down how long it took. Here is a small version that suits one box running a few services.

What a green backup log does not prove

Backups fail quietly in a handful of repeatable ways. A drill exists to catch these:

• The job backs up a path that moved after an upgrade, so it faithfully archives an empty directory.
• A live database file was copied mid-write, so the copy is torn and will not open.
• The repository is corrupt, or a pack file went missing, and nothing has read it since.
• The encryption key or passphrase lives only on the machine that just died.
• Restoring works, but takes eleven hours and you needed it in one.

Every item on that list is invisible until you try to restore. None of them produce an error at backup time.

Step 1: restore somewhere harmless

Never restore over live data for a drill. Use a scratch directory, ideally on another machine, so you also prove you can reach the repository and unlock it from somewhere that is not the original server. With restic, that looks like this:

export RESTIC_REPOSITORY=sftp:[email protected]:/srv/restic
export RESTIC_PASSWORD_FILE=/root/restic-pass   # copied from your password manager, not from the server

restic snapshots --compact
restic restore latest --target /tmp/drill

Note where the password came from. If the only copy of it is on the server being backed up, you do not have a backup, you have an expensive random-data generator.

Step 2: check the repository itself

A restore of the latest snapshot proves the files you touched are readable. It does not prove the rest of the repository is. restic check verifies the structure, and --read-data-subset actually reads and checks a slice of the stored data.

restic check --read-data-subset=10%

Reading everything with --read-data is the thorough version, but it downloads the whole repository, which may be slow or cost money on a metered remote. A rotating subset each drill covers it over time. Other tools have equivalents; borg has borg check, for instance.

Step 3: prove the data is usable, not just present

A file existing in the restore is a weak test. Check that the application could actually use it. The right check differs per service, so decide it once and script it.

For SQLite, run an integrity check on the restored file:

sqlite3 /tmp/drill/srv/app/data.db "PRAGMA integrity_check;"

That should print ok. If it does not, your backup likely copied the file while the app was writing to it.

Quick detour: why a plain file copy of a database goes wrong

Hang on, this is worth a minute. A database file is not one consistent object while the database is running. Pages are being rewritten, and in SQLite's WAL mode recent changes sit in a separate -wal file. Copy the main file alone and you can get a snapshot that never existed at any single moment.

So dump or snapshot first, then back up the result. SQLite has an online backup command, and Postgres has pg_dump:

sqlite3 /srv/app/data.db ".backup '/var/backups/app/data.db'"
pg_dump -Fc mydb > /var/backups/app/mydb.dump

Then the drill for Postgres is to restore into a throwaway database and run a query you know the answer to:

createdb drill
pg_restore -d drill /tmp/drill/var/backups/app/mydb.dump
psql drill -c "SELECT count(*) FROM users;"
dropdb drill

Compare that count with production. They will differ slightly if the dump is a day old, but they should be the same order of magnitude, and non-zero.

Step 4: time it, and write the steps down

Wrap the restore in time and note the result. This is your real recovery time, and it is usually longer than the number you carry in your head. A restore over a rural broadband link behaves very differently from one over a datacentre network.

Then write the commands in a plain text file kept somewhere that is not the server. The point is that a stressed you, at an awkward hour, with a dead machine, can follow it without remembering anything. Include:

1. Where the repository lives and how to authenticate to it.
2. Where the passphrase is kept, and who else can reach it.
3. The restore commands, in order, per service.
4. The check that proves each service came back properly.
5. The measured time from last drill.

Make it happen without relying on willpower

A drill that depends on remembering will stop happening. Put a recurring calendar entry in for once a quarter, and automate the parts you can. A systemd timer can run the subset check and the restore of a canary file, and fail loudly if either breaks.

#!/bin/sh
set -eu
rm -rf /tmp/drill
restic check --read-data-subset=5%
restic restore latest --target /tmp/drill --include /srv/app/canary.txt
test -s /tmp/drill/srv/app/canary.txt

A canary file is a small file the application touches or you update on a schedule, so a stale backup shows up as an old timestamp. It does not replace a full drill, but it catches the "empty directory for a year" case within a day. Keep the full drill for the quarterly slot.

What a first drill usually finds

Expect something to be wrong the first time. That is the point, and finding it on a Sunday afternoon with the original server still running is the cheap outcome. The usual finds are a missing config directory, a service whose data lives under a path you forgot about, a database that needs a dump step, or a passphrase nobody can locate.

Fix the one thing, rerun the drill, and note the date. Next quarter you will find a different one, because services drift. A backup is only as good as the last time somebody proved it could be read back.