Backup restore drills: a runbook for testing file, MySQL/MariaDB and PostgreSQL restores

A backup you have never restored is a hope, not a plan. Backup jobs report success while quietly missing a directory, a database, the user accounts or the encryption password, and you only find out on the day you need them. A restore drill is a scheduled, unhurried restore into a safe place, followed by checks and a written record. This runbook covers files and the two common open-source databases, and works with whatever backup tool you use.

Why drills are worth the time

Testing restores is a standard control, not an optional extra. NIST SP 800-53 Rev. 5 includes control enhancements to test backup information for media reliability and information integrity (CP-9(1)), and to use a sample of backup information to restore selected system functions as part of contingency plan testing (CP-9(2)). In Saudi Arabia, the NCA's Essential Cybersecurity Controls (ECC-1:2018 edition), which apply to government organisations and to private operators of critical national infrastructure, list periodic tests of backups' recovery effectiveness among the backup and recovery requirements. Even if neither applies to you, a drill is the only way to measure your real recovery time.

Prepare the drill

• An isolated restore target. A scratch VM or a separate database instance, never the production server or its data directories. Disable outbound email, cron jobs and payment or SMS integrations on it, so a restored application cannot contact real customers.
• A sample. Pick the latest backup and one older one, and rotate which systems you test each time.
• Success criteria written in advance. For example: the site's files and configuration restored, the orders database loads without errors, the newest order is less than 24 hours old, total time under four hours.
• A clock. Note the start time before you fetch anything; retrieval from remote storage often takes longer than the restore itself.

Drill 1: files and configuration

Restore into an empty directory and look at what came back, not just whether the command succeeded. With restic, for example:

restic snapshots --latest 3
restic restore latest --target /srv/drill --include /var/www --include /etc/nginx
find /srv/drill/var/www -type f | wc -l
find /var/www -type f | wc -l
ls -l /srv/drill/etc/nginx/sites-enabled/

Check that file counts are in the expected range, that important files open, and that ownership and permissions came back. For tar archives, tar -tzf archive.tar.gz | head lists contents before you extract with tar -xzf archive.tar.gz -C /srv/drill. Confirm that secrets and configuration outside the web root, such as .env files, TLS keys and systemd units, are in the backup too; they are the items most often left out.

Drill 2: MySQL or MariaDB

Install the same major version as production on the drill machine, then load the dump and query it:

sudo apt install mariadb-server
zcat shop-2026-10-31.sql.gz | sudo mariadb
sudo mariadb -e "SHOW DATABASES;"
sudo mariadb shop -e "SELECT COUNT(*), MAX(created_at) FROM orders;"

The newest timestamp in a busy table tells you the recovery point you actually achieved: if it is from two days ago, your nightly backup is not running nightly. If you use physical backups (mariadb-backup or Percona XtraBackup), the drill must include the prepare step and starting a server on the restored data directory, since an unprepared copy is not usable. If you rely on binary logs for point-in-time recovery, replay them to a chosen time at least once a quarter.

Drill 3: PostgreSQL

Restore global objects first, then the database, and run the same kind of queries:

sudo -u postgres psql -f globals-2026-10-31.sql
sudo -u postgres createdb shop_drill
sudo -u postgres pg_restore -d shop_drill -j 4 shop-2026-10-31.dump
sudo -u postgres psql -d shop_drill -c "SELECT count(*), max(created_at) FROM orders;"

Errors about missing roles usually mean the globals were never backed up. For physical backups, run pg_verifybackup on the base backup, then start a cluster from it on the drill machine; the PostgreSQL manual notes that verification does not replace test restores. If you archive WAL, recover to a chosen recovery_target_time occasionally.

Drill 4: the application

Data that loads is not the same as a working service. Point a staging copy of the application at the restored database and files, log in, open a recent record and run one normal transaction. This is where missing environment variables, API keys and file uploads show up.

What to record

Keep a short record for every drill, in the same place each time:

Compare the measured times with the recovery time and recovery point objectives in your disaster recovery plan. If the drill took six hours and the plan promises two, the plan needs to change, or the backup design does.

How often

NIST leaves the testing frequency to each organisation, so set one that matches the value of the system. A reasonable starting point for a small company: a sample file and database restore every month, a full rebuild of one server from backups every quarter, and an extra drill after any change to the backup tool, encryption keys, storage provider or database major version. On a self-managed VPS, such as a Skyline Cloud server in Riyadh where the platform takes no snapshots or server backups today, these drills are the only evidence that your own backups work.

What you should do

1. Set up an isolated drill machine with outbound email and scheduled jobs disabled.
2. Write down success criteria and the recovery targets you are testing against.
3. Restore files, each database and the application, and check content, not just exit codes.
4. Record times, the newest data found and every problem, and assign follow-ups.
5. Repeat monthly for samples and quarterly for a full rebuild, and after every change to the backup setup.

Sources

• csrc.nist.gov/pubs/sp/800/53/r5/upd1/final
• raw.githubusercontent.com/usnistgov/oscal-content/main/nist.gov/SP800-53/rev5/json/NIST...
• nca.gov.sa/ecc-en.pdf
• restic.readthedocs.io/en/stable/050_restore.html
• restic.readthedocs.io/en/stable/045_working_with_repos.html
• github.com/restic/restic/blob/v0.19.1/cmd/restic/cmd_snapshots.go
• mariadb.com/docs/server/server-usage/backup-and-restore/mariadb-backup/full-backup-and-...
• mariadb.com/docs/server/mariadb-quickstart-guides/mariadb-restore-guide
• postgresql.org/docs/current/app-pgrestore.html
• postgresql.org/docs/current/app-pgverifybackup.html
• postgresql.org/docs/current/continuous-archiving.html
• cloud.alskyline.com/vps-saudi-arabia