Restore tests: a backup you have never restored is only a hope

Most backup dashboards are green. They report that a job ran and a file was written, which says nothing about whether you can get your business back from that file, how long it takes, or whether the copy survives an attacker with admin rights. The only proof is a restore. On the platforms I run, backups are written every few hours outside the hosting provider and restored every night into an isolated twin, automatically. This is how to set that up.

Dominik Heinrich

November 8, 20269 min read

1. Decide what "restored" means

Two numbers per system frame every backup decision. The recovery point objective (RPO) is how much data you can afford to lose: if bookings come in all day, four hours of lost bookings may already be too much. The recovery time objective (RTO) is how long the business can wait until the system is back. NIST's contingency planning guide for IT systems uses exactly these terms, and they are worth writing down with the business owner, not guessed by the platform team.

The RPO sets the backup frequency; the RTO sets how fast a restore must be, and later in this article it becomes the threshold your nightly test is measured against.

2. Put backups out of reach

A backup that lives in the same account, with the same admin credentials, falls with the system it protects: a provider outage, a deleted subscription or an attacker who reaches the admin role takes both. So encrypt the backup before it leaves, write it to storage outside the hosting provider or at least outside the account, and make it immutable. Azure Backup's immutable vault prevents recovery points from being deleted before they expire, and Amazon S3 Object Lock stores objects write-once-read-many with a retention period.

Give the backup job credentials that can write but not delete. On one platform I run, even the audit trail is written that way: S3 Object Lock, kept 90 days, with write-only credentials, so a compromised application can add entries but never remove them.

Tip from practice

Keep at least one copy with a different provider or region and different credentials. Two copies behind the same password are one copy.

Restore test architecture: production with an application and a PostgreSQL database writes encrypted backups every few hours to immutable storage outside the provider with a second copy in another region or provider; every night a restore job creates a fresh database in an isolated twin, checks row counts, checksums and an application smoke test, measures the restore time against the RTO and alerts a person if any check fails. — Backups out of reach, restored every night into an isolated twin, checked, timed and alerting a person when they fail.

3. Restore every night into an isolated twin

Make the restore a scheduled job, not an emergency procedure. Every night it takes the latest backup, creates a fresh database in an isolated environment with no network path to production, and restores into it. Isolation matters twice: a test must never touch live data, and a restored copy holds the same personal data as production, so it gets the same protection and is deleted after the checks.

The tooling exists on every platform. AWS Backup has restore testing, which runs restore jobs on a schedule and tracks how long they take. On Kubernetes, CloudNativePG can bootstrap a new PostgreSQL cluster from a backup. A plain script with pg_restore into a throwaway database works everywhere and is easy to read at three in the morning.

bash

#!/usr/bin/env bash
# Nightly restore test: latest backup -> fresh database in the isolated twin -> checks
set -euo pipefail
LATEST=$(ls -1 /backups/db/*.dump.gpg | sort | tail -1)   # synced from the off-site store
gpg --batch --decrypt --output /tmp/latest.dump "$LATEST"

START=$(date +%s)
dropdb --if-exists -h restore-twin restore_check
createdb -h restore-twin restore_check
pg_restore -h restore-twin -d restore_check --no-owner --jobs 4 /tmp/latest.dump
echo "restore_seconds $(( $(date +%s) - START ))"            # compare with the RTO

psql -h restore-twin -d restore_check -v ON_ERROR_STOP=1 -f checks.sql
rm -f /tmp/latest.dump

4. Check the data, not the exit code

A restore can finish without an error and still be useless: a table that was never in the backup, a truncated dump, a copy that is three days old because the backup job silently failed. So compare the result with production. Exact row counts of the key tables against a production replica, a checksum over the rows of the tables that matter, ordered so that physical layout doesn't change the result, and the newest timestamp, which must be within the RPO.

The same checks are what make migrations safe. When I moved tenant databases into a Kubernetes cluster with CloudNativePG, row counts and checksums had to be identical before anything switched over, and they were.

sql

-- checks.sql: run the same queries on a production replica and compare
-- 1. Exact row counts of the key tables
SELECT 'bookings' AS tbl, count(*) FROM bookings
UNION ALL SELECT 'payments', count(*) FROM payments
UNION ALL SELECT 'customers', count(*) FROM customers;

-- 2. Checksum independent of physical row order
SELECT md5(string_agg(b::text, '|' ORDER BY b.id)) AS bookings_checksum
FROM bookings b WHERE b.created_at < date_trunc('day', now());

-- 3. Freshness: the newest row must be inside the RPO
SELECT now() - max(created_at) AS data_age FROM bookings;

Tip from practice

Checksum only rows older than the backup's start, as in the example. Otherwise every new booking in production makes the comparison fail for the wrong reason.

5. Run the application against the restore

Data that looks right can still be unusable: a database extension that isn't installed in the restore target, roles and grants that the dump didn't carry, secrets that only exist in production. A short smoke test catches these: start the application against the restored database and let it log in, read a record and write one. If that works, you have a system back, not just a set of tables.

Keep the smoke test small and deterministic. It is not a full test suite; it answers one question every morning: could we run the business on last night's backup?

6. Measure the restore time against the RTO

Databases grow, and so does the time it takes to bring them back. A restore that took twenty minutes last year may take two hours now, and nobody notices until the day it matters. Record the duration of every nightly restore, from the start of the download to the passed smoke test, and chart it next to the RTO.

When the line approaches the target, act before an incident does it for you: parallel restore jobs, a faster storage tier for the twin, a different backup method for the largest tables, or an honest conversation about a longer RTO.

7. Alert on failed restores, and practise with people

Most monitoring alerts when a backup job fails. Alert when a restore fails, too: a failed check, a smoke test that doesn't pass or a duration above the RTO pages a person in the morning, in a channel someone reads. A backup that can't be restored is an outage you just haven't had yet.

Automation proves the data; it doesn't prove the people. Once a quarter, walk through a restore with the team using the runbook, including who decides, who communicates and where the credentials are. The checklist below lists this among the basics a platform needs before it calls itself production-ready.

Takeaway
Backups are a promise; restores are the proof. Restore every night, check the data and the app, time it against the RTO, and let a failed restore wake someone up.

Real projects behind this

Monitoring on business outcomes, backups proven nightly

Want this in your company? Let's talk mail@dominikheinrich.ch

Sources and official guides