Proving a Database Backup Can Actually Be Restored

A backup job can finish without error while still producing a file that's corrupted, incomplete, or encrypted with a key nobody can find anymore. Permissions issues, a schema change that broke the backup script silently, or a storage quota that truncated the file are all failures a job's own exit code won't catch, because the job's job is to run the backup command, not to prove the output is usable. The only way to know a backup actually restores is to restore it. Treat a backup log full of green checkmarks as an unverified claim, not a guarantee, until an actual restore has confirmed it.

Spin up a separate environment, ideally as close to production configuration as practical, and restore your most recent backup into it end to end: same database engine version, same expected schema, same access controls. Isolating the drill from production means you can run it on a schedule without any risk to live data, and it forces you to actually exercise the full restore process, including any manual steps your runbook currently glosses over.

A restore that completes without error can still leave you with data from the wrong point in time, missing recent transactions, or silently corrupted rows if the backup itself was already bad. Run a specific verification query after every drill: row counts against a known baseline, a checksum on a critical table, or confirming the most recent expected transaction is actually present. Treat this step as mandatory, not optional, since it's the step that actually confirms the backup did its job.

How long a full restore takes, from the moment you decide to restore to the moment the system is serving traffic again, is the number that matters during a real incident, not how long the nightly backup job takes to run. Time your drill end to end and compare it against whatever recovery time you've promised internally or to customers. A backup strategy that technically works but takes fourteen hours to restore isn't meeting a recovery target measured in one, and the only way to find that gap is to time an actual drill. Write the measured time down after every drill so a slow creep, a growing database that quietly extends restore time month over month, gets caught before it turns your recovery target into fiction.

For example, suppose a nightly backup takes minutes to run, but a full restore into a clean environment takes many hours, mostly waiting for data to load and indexes to rebuild. Nobody knew until the first drill, because only the backup was ever timed. The team then compared the measured restore time with its recovery target, found the gap, and changed its approach before an incident forced the issue. The common mistake is timing the backup instead of the restore. Record the restore time after every drill so gradual growth shows up as a trend.

A restore drill that only one engineer has ever run is a single point of failure wearing a checklist. Rotate ownership of the drill across the team on a schedule, and keep the runbook current enough that someone running it for the first time can follow it without asking the usual person for help. If the drill only succeeds because the same experienced engineer always runs it, that's a finding worth writing down, not a reason to stop rotating.

A single successful drill proves the process worked on one specific day, not that it will work again after the next schema migration or infrastructure change. Quarterly is a reasonable cadence for most small teams, and pair it with an ad hoc drill after any significant change to your database schema, backup tooling, or storage provider, since that's exactly when a previously working restore process quietly breaks.

What a restore drill involves:

1. Restore the most recent backup into an isolated environment that matches production configuration and database engine version.
2. Run verification queries, such as row counts against a baseline, a checksum on a critical table, or the latest expected transaction.
3. Time the whole process from the decision to restore to serving traffic, and compare it with your recovery target.
4. Write down the measured time and any manual steps the runbook glossed over.
5. Rotate who runs the drill, and repeat it on a regular cadence and after major changes.