Backups That Never Restored: Automated Restore Drills

The backup job had been green for fourteen months. Every night at 02:00 it ran mysqldump, gzipped the output, pushed it to object storage and posted a tick to the ops channel. Then a developer ran a migration against the wrong connection and dropped a tenant table. We pulled last night's dump, started the restore, and watched it fail at 61%: the dump had been taken without --single-transaction on a set of InnoDB tables that were being written during the run, and a later change had added a view referencing a table that was dumped after it. Every backup for months had the same problem. The job never failed because writing a broken file is still a successful write.

I wrote this with help from AI tools and checked every command, script and config myself on MySQL 8 and PostgreSQL 16. I am a Principal Software Engineer in Karachi with 9+ years across PHP/Laravel, Vue, Node and .NET microservices, and at Loop Origin we run and maintain production servers for SaaS clients, where a backup only counts once it has been restored. This is Part 5 of the series Silent Failures in Production SaaS. Part 1 covered cron jobs that never ran, Part 3 covered email that never landed and Part 4 covered config and flag drift. Backups are the silent failure you only discover on your worst day.

📚 Series Roadmap — Silent Failures in Production SaaS

• Part 1: Cron Jobs That Never Ran: Heartbeats & Dead-Man Switches for SaaS
• Part 2: Webhooks That Stopped Arriving: Gap Detection & Reconciliation
• Part 3: Email That Never Landed: SPF/DKIM/DMARC Drift & Bounce Pipelines
• Part 4: Config & Feature Flags That Quietly Diverged Across Environments
• Part 5 (This Post): Backups That Never Restored: Automated Restore Drills
• Part 6: Synthetic Checks & SLOs for the Paths Nobody Clicks

Related earlier posts: Zero-Downtime Database Migrations in Laravel & Node.js SaaS · Production Observability on a Budget · Delayed Jobs, Schedulers & Clock Skew Gotchas

How do you know your database backups will actually restore?

Direct answer: Only by restoring them. Schedule an automated drill that pulls the latest backup into a throwaway database, restores it, runs integrity checks such as row counts, checksums and a few application queries, measures restore time against your RTO, and alerts on any failure. A backup job's exit code proves a file was written, not that it is usable.

Why backup jobs lie

A green backup job tells you a process exited with status zero. It does not tell you any of the following, and I have seen each one in client systems:

The last row is common in Laravel apps that store uploads on the local disk. The database restores, and every invoice PDF link returns 404.

Step 1: take backups that can restore

For MySQL with InnoDB, a consistent logical dump needs a transaction snapshot and the objects people forget:

mysqldump --single-transaction --quick --routines --triggers --events \ --set-gtid-purged=OFF --hex-blob app_production \ | gzip -9 > "app_$(date -u +%Y%m%dT%H%MZ).sql.gz" sha256sum app_*.sql.gz > SHA256SUMS 

For PostgreSQL, the custom format is compressed, restorable in parallel and lets you restore selected tables:

pg_dump --format=custom --no-owner --file="app_$(date -u +%Y%m%dT%H%MZ).dump" app_production 

For larger databases, logical dumps get slow. Physical backups with point-in-time recovery (Percona XtraBackup for MySQL, pgBackRest or WAL archiving for Postgres, or your cloud provider's snapshots plus binlog/WAL retention) restore much faster. The drill below works the same way regardless of method.

Upload the checksum file next to the backup and verify it after upload by reading the object back, not by trusting the upload command.

Step 2: the restore drill script

This is a trimmed version of the drill we run for PostgreSQL clients. It restores into a disposable container, checks integrity and reports timings:

#!/usr/bin/env bash set -euo pipefail BUCKET=s3://acme-backups/postgres WORK=$(mktemp -d); trap 'docker rm -f drill-pg >/dev/null 2>&1; rm -rf "$WORK"' EXIT start=$(date +%s) latest=$(aws s3 ls "$BUCKET/" | sort | tail -1 | awk '{print $4}') aws s3 cp "$BUCKET/$latest" "$WORK/db.dump" --only-show-errors age_h=$(( ( $(date +%s) - $(date -d "$(aws s3 ls "$BUCKET/$latest" | awk '{print $1" "$2}')" +%s) ) / 3600 )) [ "$age_h" -le 26 ] || { echo "FAIL: newest backup is ${age_h}h old"; exit 1; } docker run -d --name drill-pg -e POSTGRES_PASSWORD=drill -v "$WORK:/w" postgres:16 >/dev/null until docker exec drill-pg pg_isready -q; do sleep 1; done docker exec drill-pg createdb -U postgres app docker exec drill-pg pg_restore -U postgres -d app --no-owner --jobs=4 /w/db.dump q() { docker exec drill-pg psql -U postgres -d app -tAc "$1"; } users=$(q "select count(*) from users") newest=$(q "select extract(epoch from now() - max(created_at))/3600 from orders") fk_bad=$(q "select count(*) from order_items oi left join orders o on o.id = oi.order_id where o.id is null") [ "$users" -gt 1000 ] || { echo "FAIL: users=$users"; exit 1; } [ "${newest%.*}" -le 30 ] || { echo "FAIL: newest order ${newest}h old"; exit 1; } [ "$fk_bad" -eq 0 ] || { echo "FAIL: $fk_bad orphan order_items"; exit 1; } echo "OK backup=$latest age=${age_h}h users=$users restore_s=$(( $(date +%s) - start ))" 

Three checks matter more than any others: freshness (is the newest data recent enough to meet your RPO), plausibility (row counts are in the expected range, not zero) and referential sanity (no orphaned rows, which is what an inconsistent dump produces). Then add one or two queries your application really depends on.

Step 3: schedule it and make silence an alert

The drill is itself a cron job, which brings us back to Part 1: it needs a heartbeat. In Laravel, I schedule it weekly on a separate worker and ping a dead-man switch only on success:

// routes/console.php (Laravel 11+) use Illuminate\Support\Facades\Schedule; Schedule::exec('/opt/ops/restore-drill.sh') ->weeklyOn(0, '03:30') ->onOneServer() ->appendOutputTo(storage_path('logs/restore-drill.log')) ->pingOnSuccess(env('DRILL_HEARTBEAT_URL')) ->emailOutputOnFailure('ops@example.com'); 

If the heartbeat does not arrive within the window, the monitoring service alerts. That covers the drill failing, the server being down and the schedule silently not running. (In real code, read the URL through config() rather than env(), for the reason covered in Part 4.)

Step 4: track RTO and RPO as numbers

Log every drill result: backup age, restore duration, database size. Plot them. Restore time grows with data, and the day it crosses your recovery time objective should be a planned conversation, not an incident discovery. On one client platform, restore time crept from 25 minutes to 2 hours 40 minutes in a year; we moved to physical backups with WAL archiving before it became a problem.

Run a full human drill quarterly as well: a person follows the written runbook, end to end, including DNS, uploads, queues and cache warm-up. Automation verifies the data; the human drill verifies the process.

This is a standard part of our server setup and maintenance work, and it is included in the maintenance tiers on our pricing page, because an untested backup is a cost with no guaranteed benefit.

Diagnostic checklist

• [ ] Dumps use --single-transaction (MySQL) or a consistent method, and include routines, triggers and events.
• [ ] Checksums are verified after upload by reading the object back.
• [ ] User uploads and other files are backed up, not just the database.
• [ ] An automated restore drill runs at least weekly into a disposable database.
• [ ] The drill checks freshness, row-count plausibility and referential sanity.
• [ ] The drill has a heartbeat; silence alerts.
• [ ] Restore duration and backup age are logged and compared to RTO and RPO.
• [ ] Encryption keys for backups are stored and tested separately from the backups.
• [ ] Retention rules are reviewed and at least one older backup is restored per quarter.
• [ ] A human follows the restore runbook end to end every quarter.

FAQ

Are managed database snapshots enough?

They are a good base, but you should still restore one regularly into a new instance and run checks. Snapshots can be misconfigured, have short retention, or live in the same account an attacker or mistake can delete.

How often should restore drills run?

Weekly for automated drills is a sensible default for most SaaS. Daily if your data changes fast and your RPO is tight.

Won't the drill cost a lot?

A disposable container or a small instance for an hour a week is cheap compared to one failed restore. For very large databases, restore a recent subset daily and a full copy monthly.

Should the drill restore production data into a less secure environment?

No. Run it in an isolated environment with the same access controls as production, and destroy it after the checks.

What about point-in-time recovery?

Drill it too: restore to a specific timestamp and check that a known row written just before that time exists and one written just after does not.

How do I back up Laravel storage files?

Use an S3-compatible disk for uploads with versioning enabled, or sync the local disk to object storage on a schedule, and include a sample file check in the drill.

Next and last in this series, Part 6 covers synthetic checks and SLOs for the paths nobody clicks.

Follow & Connect

• Website: Loop Origin
• LinkedIn: Loop Origin
• X: Muhammad Ali Mughal (@AliMughal887)
• GitHub: Loop Origin (@LoopOrigin)