Restore Drills
The only evidence a backup works: restore it into a real target and verify the application against it. Backup success is not a signal.
The question, the obvious approach, and why it breaks
Every lesson starts where the work starts: an operational problem, a first attempt that is entirely reasonable, and the way production disagrees with it.
Our backup job succeeds every day. Are we safe?
Backup and restore are different systems that share a name. The backup path runs daily and is exercised constantly; the restore path runs never, and is first exercised on the worst day of the year, by a tired person, under time pressure.
The backups are green and the provider is reputable. If we ever need a restore we will follow the documentation then — it is a supported operation, so it will work.
The restore path has dependencies nobody has ever exercised: a decryption key, an account permission, a network route to the archive, disk space at the target, a matching engine version.
- The restore path has dependencies nobody has ever exercised: a decryption key, an account permission, a network route to the archive, disk space at the target, a matching engine version.
- Restore time is discovered during the incident, and it is almost always longer than the number in the recovery objective — often by an order of magnitude, because it is dominated by moving data rather than by running a command.
- A restore that produces a running database is not a recovered system. Application-level checks — can users log in, are recent orders present, do foreign keys resolve — routinely fail after a technically successful restore.
- Nobody knows who does it. The procedure lives in one engineer's memory, and the incident is on a night they are unreachable.
- The first restore attempt reveals that the backup covers the database but not the object storage the rows reference, so the data is there and the product is still broken.
What is actually happening
Underneath the tooling, which is the part that survives a change of tool.
- A drill converts an assumption into a measurement. Before the drill, restore time and restore viability are estimates; after it, they are observations with a date attached.
- Restore time decomposes into: locate the copy, provision a target, transfer the bytes, load and rebuild indexes, replay the log forward to the chosen point, then verify. Transfer and index rebuild usually dominate, which is why restore duration scales with data volume rather than with the complexity of the command.
- The verification step is what makes it a drill rather than a file copy. The claim being tested is "the application works against this data", and only the application can make that claim.
- Drills also test the humans and the documentation. A procedure that only one person can execute has a single point of failure that no amount of redundancy in the storage layer addresses (Runbooks).
- Because a drill restores into a *separate* target, it is safe to run in production hours — it competes for network and archive throughput, not for the primary.
The drill, phase by phase, with what counts as evidence
Each phase has its own failure mode, and each has a signal that distinguishes "it ran" from "it worked". A drill that skips the last phase is a file transfer test.
- 1Select
Choose the real production backup and the recovery point being tested.
fails by Drilling a copy made for the drill, which is not the artefact production depends on.
evidence The backup identifier and its source instant are recorded before the drill begins.
- 2Provision
Create a clean, isolated target with no prior state.
fails by Reusing a target that already has schema or data, masking gaps in the backup.
evidence Target created within the drill; a pre-check asserts it is empty.
- 3Transfer
Move the backup to where the restore will run.
fails by Missing permission, missing network route, missing decryption key, insufficient disk.
evidence Bytes transferred match the manifest; phase timed separately, because this phase usually dominates.
- 4Restore
Load the copy into the engine.
fails by Engine version mismatch, missing extensions, roles or grants, unexpected index rebuild time.
evidence Engine opens the data and reports a consistent state; phase timed.
- 5Replay
Apply the archived log forward to the chosen instant, where point-in-time recovery is in use.
fails by A gap in the log chain, silently truncating how far forward you can go.
evidence The engine confirms recovery to the requested target instant, not merely to the end of what it had.
- 6Verify
Start the application against the restored data and assert business-level truths.
fails by Checking that the database accepts connections — which an empty database also does.
evidence Named assertions pass: a known user authenticates, a recent order is present, referential checks resolve, a report returns a plausible result.
- 7Record
Write down phase timings, volume, operator, gaps found.
fails by A verbal "it worked", which cannot be compared against the recovery objective.
evidence A dated drill record that someone else could read and act on.
- 8Tear down
Destroy the target and its copy of production data.
fails by Leaving an unmonitored production dataset behind.
evidence Target confirmed deleted; the deletion is part of the drill result.
Only the Verify step produces the claim people think the backup job was making.
Three levels of drill, and what each one actually proves
Teams often argue about whether their testing is sufficient. The argument gets shorter once it is phrased as what the test proves and what it leaves unproven.
The levels are cumulative in cost and in confidence. Running the cheapest one weekly and the most expensive one rarely is a defensible position; running only the cheapest and calling it a restore test is not.
SELECT 1; SELECT count(*) FROM users; -- "looks about right"
-- identity: a known account still authenticates end to end -- recency: the newest row predates the recovery point by a -- plausible margin, and nothing postdates it -- integrity: sampled foreign keys resolve; no orphaned children -- external: rows referencing object storage resolve to objects -- that exist in the restored bucket -- application: log in, load a dashboard, place a test order
A row count passes for a backup restored from the wrong day, for a partially loaded table, and for data whose referenced files no longer exist. Each assertion on the right fails for a specific, real recovery defect — and the last one is the only check that exercises the application's own assumptions about its data.
| Level | What it proves | What it still does not prove |
|---|---|---|
| Structural check of the archive | The file exists, opens, and looks complete | That any of it loads into an engine |
| Restore to a target | The data loads, the engine starts, restore time at this volume | That the application can use the data |
| Restore + application verification | A recovered system serves correct data, end to end | That a human can do it from the runbook under pressure |
| Unrehearsed drill by a rotating operator | The documented procedure is sufficient for someone else | That it holds after the next upgrade — drills expire |
What goes wrong during a drill — which is the good outcome
These are not hypotheticals; they are the recurring findings of first drills. Every one of them would otherwise have been discovered during an actual data-loss event.
Treat each as a defect with an owner and a fix, and re-drill afterwards. A drill that finds nothing on the first run usually means the verification was too weak.
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| Restore attempted from an account without archive read permission | Access denied before any data moves | The recovery identity was never granted what the backup identity has | Grant and test a dedicated recovery role; include the permission check in the drill pre-checks |
| Encrypted archive, key held only in the lost environment | Bytes present, unreadable | Key management designed for the backup path, not the recovery path | Store the key material in an independent domain and drill decryption from a machine with no production access |
| Target instance smaller than production | Restore fails on disk, or index rebuild takes far longer than planned | Recovery capacity was never provisioned or costed | Record the required target size as part of the recovery plan (Capacity During Failover) |
| Restore succeeds; application fails at startup | Missing roles, extensions, sequences or grants | The backup captured data, not the surrounding database objects | Include database-level objects in the backup and assert them in verification |
| Data restored; product still broken | Records reference files that do not exist | Object storage was never in scope and never in the plan | Bring the referenced store into the same recovery point, or document the inconsistency as an accepted outcome |
| Point-in-time recovery to a chosen instant | Engine stops short of the requested point | A gap in the archived log chain nobody was alerting on | Monitor archive continuity as a first-class signal, not as a property of the backup job (Backup Operations) |
How to do it properly
Most important first.
- Restore from the real backup, not from a copy made for the drill. The artefact under test is the production backup.
- Restore into a clean, isolated target that has never held this data. A target with leftover state can pass a check that a genuine recovery would fail.
- Finish with application verification, not with a row count: start the application against the restored data and run a small set of business assertions.
- Time each phase separately and record them. The breakdown is what tells you whether an objective is achievable and which phase to attack (RTO and RPO).
- Rotate the operator. A drill executed by the person who wrote the runbook tests the storage; a drill executed by someone else tests the runbook.
- Include the non-database state — object storage, search indexes, message state — or state explicitly, in writing, that they are not recoverable.
- Treat drill failure as an incident with a postmortem, because it is a real defect that happened to be found on a good day (Postmortems).
How much can this affect
Every production change has a blast radius. Stated as a scale so it is comparable between changes rather than adjectival — and paired with what actually contains it, because a wide scope with a real containment mechanism is a different situation from a wide scope with none.
Nothing — this is why restore drills exist. An untested restore path is the one production risk with no mitigation available after the fact.
What can go wrong
- The drill restores to a target that already has the schema, so a missing-schema bug in the backup is masked.
- Verification checks that the database accepts connections, which passes for an empty database.
- A successful drill is treated as permanent evidence, and the next engine upgrade, schema change or account policy change silently invalidates it.
- "The backup job is green, so we are covered." Green means the backup process finished. It is the input to recovery, not evidence of it. This is the single most expensive misreading in the domain.
- "We restored last year, so restore works." Restore works for the system as it was last year. Engine versions, data volume, schema, permissions and account policy have all moved since.
- "The database came up, so the drill passed." Coming up proves the engine can open the files. Verification is an application-level claim.
- "Automated restore testing means we no longer need a runbook." The automation is the happy path; the runbook is for when the automation is part of what is broken.
Operating it
Evidence is the signal, not the intention. Rollback is sometimes 'you cannot, and that is the point'.
- A dated record per drill: which backup, which target, wall-clock time per phase, the data volume, the operator, and the application assertions that passed.
- Measured restore time compared against the stated recovery objective, with the gap named as a defect when it exists.
- At least one drill executed by someone who did not write the procedure and did not need help.
- A drill that ran after the most recent major engine upgrade or backup-configuration change.
- A drill needs no rollback if the target is isolated — that isolation is the design requirement, not a convenience.
- The one destructive mistake to guard against is restoring over a live system by pointing at the wrong target. Make the target explicit in the procedure, require it to be named, and prefer credentials that cannot write to production at all.
- Tear the target down afterwards, and treat the teardown as part of the drill rather than as cleanup someone will get to.
- Automate: provisioning the target, fetching the backup, running the restore, running the verification suite, timing each phase, tearing the target down, and reporting the result.
- Keep human: at least one drill per period executed by hand from the written runbook, because an automated drill validates the script and not the document a person will read at 3am.
- Drilling at production data volume costs real transfer and storage; drilling at reduced volume is cheaper and teaches you nothing about duration.
Where this applies
This domain is unusually tool- and organisation-dependent. These labels say what each claim is specific to, and what a different platform, provider or organisation does instead.
- GENERALBackup -> restore -> application verification is the required chain for any datastore. What varies is the mechanism; what does not vary is that only the third step is evidence.
- DATABASE-SPECIFICWhat "restore" means differs: replaying archived WAL onto a base backup for PostgreSQL point-in-time recovery, loading a logical dump and rebuilding indexes, or attaching a provider snapshot as a new instance. Index rebuild dominates the logical path and does not exist in the snapshot path.
- CLOUD-SPECIFICManaged snapshot restores create a new instance, so the drill also exercises quota, subnet, parameter group and security group configuration — infrastructure that is part of your recovery path and frequently the part that fails first.
Where the depth lives
This domain teaches delivery and operation, and hands the mechanism off to the domain that owns it.
- — Testing & Reliability Engineering — drills as a form of test whose subject is the operational procedure rather than the code.