A backup is only real after a restore

Almost everyone has backups. Far fewer teams know how long a restore takes, whether the files are complete, or whether the database dump even loads. The only way to find out is to restore, regularly, and without a human having to remember.

What a useful backup policy answers

• How much data can we lose? (recovery point)
• How long can we be down? (recovery time)
• Where does the second copy live? At least one copy must be off the server and out of reach of the credentials that run the server.

A small, honest restore test

The idea is simple: dump, load into a scratch database, run a sanity query, and fail loudly if anything is off.

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

STAMP=$(date +%F)
DUMP=/backup/db/app-$STAMP.sql.gz

# 1. create the backup (consistent snapshot for InnoDB)
mysqldump --single-transaction --routines --triggers app | gzip > "$DUMP"
gzip -t "$DUMP"

# 2. restore into a throw-away database
mysql -e "DROP DATABASE IF EXISTS restore_test; CREATE DATABASE restore_test"
gunzip -c "$DUMP" | mysql restore_test

# 3. sanity check: the data must look like data
ROWS=$(mysql -N -e "SELECT COUNT(*) FROM restore_test.users")
[ "$ROWS" -gt 0 ] || { echo "restore test failed: users is empty" >&2; exit 1; }

mysql -e "DROP DATABASE restore_test"

Run it from cron or a systemd timer, and send the failure output somewhere a person will see it.

Details that bite later

1. Encrypt copies that leave the server, and store the key separately.
2. Keep several generations. Corruption and accidental deletion are often noticed days late.
3. Back up configuration, not just data: web server, PHP, cron, firewall rules and DNS records.
4. Time a full restore at least twice a year and write the number down. That number is your real recovery time.