I have watched a team lose a database on a Friday evening and get it back before dinner, and I have watched a team lose one on a Tuesday morning and stay down until Thursday. The difference was not luck. It was a plan they had written down, backups they had actually restored, and a drill they had run more than once. This lesson builds disaster recovery from scratch: you define what you can afford to lose, you automate the backups, you prove the restore works, and you keep proving it. Nothing here is theoretical. Every step is a thing I have done on call, and the timings are a worked example from one small system, not a measurement.
Every hop costs time, and this lesson counts it in minutes and hours, not milliseconds. The bar below is a worked example for one small system: a primary database, a standby in a second region, a team that drills on a schedule. It is not a measurement from a live system. The shape is the honest part. The restore and the failover eat most of the time, and the planning hops are cheap. Switch the scenario and watch the bar change. In the real outage, the failover doubles. In the untested case, the restore eats seven hours and the failover never runs at all.
The budget above is one fixed plan. This box is yours. Back up less often and watch the data loss window grow. Test restores less often and watch the confidence fall. Then ask the question I ask before every drill: if the primary region dies tonight, how much do we lose, how long are we down, and do I actually believe the restore works?
How often a backup is taken. It sets the data loss window: lose the database right before the next backup, and you lose everything since the last one.
How often you prove a restore works. The longer the gap, the older your proof.
How long it takes to move traffic to the standby. Measure it in a drill, do not guess it.
Formula, stated plainly: max data loss equals the backup frequency. Max downtime equals failover time plus a 30 minute restore estimate for this example database. Confidence starts at 100, minus half a point for each day since the last restore test, minus one point for every 6 minutes of failover time, clamped between 5 and 99. At the defaults: 100 minus 15 minus 10 equals 75 percent.
Extra diagram. I read the picture first: the boxes are the system, the moving dot is a backup copy, and each step adds one piece until the full picture is there. No text needed to follow it.
Extra diagram. I read the picture first: the boxes are the system, the moving dot is a failover drill, and each step adds one piece until the full picture is there. No text needed to follow it.
Three questions. No score is saved anywhere — this is just you against the material.
The lesson above builds a disaster recovery plan for one small system. This section covers the same plan four times, from the first backup to the level where recovery is proven, not promised.
Definition: A backup is a copy of the data that lives somewhere the disaster cannot reach. If the database and its backup sit in the same account, the same region, the same building, one bad day takes both. I learned that the cheap way, in a drill, not in a real outage.
Types:
Uses: I use this level the day I take over a service. Before I change anything, I check the backup exists, I check where it lives, and I check the restore works. A backup nobody has restored is a hope, not a backup.
Dimensions:
Definition: RTO is how long I have to get the service back. RPO is how much data I am allowed to lose. Those two numbers are the plan. Everything else is how I meet them. A plan without numbers is a wish list.
Types:
Uses: I use this level when the business asks what a bad day costs. I answer with the two numbers and the last drill time. "We can lose fifteen minutes of data and be back in four hours. The last drill took three."
Dimensions:
Definition: A backup restores the data. Failover keeps the service running. The standby takes over, the traffic moves, and the users see a blip instead of an outage. It only works if I have done it before, under pressure, with the same people who will do it for real.
Types:
Uses: I use this level when downtime has a price. The drill is the proof. I run it before the business asks, because the business asks after the outage, and then it is too late.
Dimensions:
Definition: At this level disaster recovery is not a document. It is a program with a calendar, an owner, and a report. Backups are tested by automation, restores are tested on a schedule, failover drills are quarterly, and the results go to leadership. The plan is proven, not promised.
Types:
Uses: I use this level when the company depends on the plan. The program owner is a named person. The report says which services passed their drill and which did not. A service that fails its drill is a service with a known gap, and the gap has a date.
Dimensions:
The lesson above is one small system with a plan that works. Production is the rest of it. Here is what I actually spend my time on once the backups are running and the first drill is done.
A full backup is the whole database, every time. The restore is one step: load the file, done. The cost is time and space, and for a big database the full backup never finishes inside its window. An incremental backup is only what changed since the last backup. It is small and fast, and the restore is a chain: full, then every incremental after it, in order. If one link in that chain is broken, the whole restore fails, and that is exactly what happens in the incident below. A snapshot is the disk frozen in a moment. It is fast to take and fast to restore, but it is a picture of the disk, not a clean database file, and it usually lives in the same cloud account as the thing it protects. I use all three, for different jobs. Full weekly, incremental every 15 minutes, snapshot before a risky deploy. The mix is the strategy. One type alone is a gamble.
I do not test restores by hand any more. A hand-tested restore gets skipped the month everyone is busy, which is the month it matters. I run a job on a schedule: take the latest backup, restore it to a scratch database, count the rows, check the newest record, time the whole thing, and post the result where the team can see it. If the restore fails, the job pages me, the same as any other production alert. A failed restore test is a production incident that has not happened yet. I treat it like one. The script below is the shape of it, not a copy-paste answer: every database has its own restore command, and the checks are the part that matters, not the syntax.
Every minute of RTO and every minute of RPO has a price. An RPO of zero means continuous replication, and continuous replication means a standby that is always running, always paid for, and always watched. An RTO of five minutes means the standby is hot, the DNS is pre-staged, and the failover is a button, not a runbook. That costs real money every month, even in the months nothing breaks. An RTO of a day means a cold backup in cheap storage and a restore you run by hand, and that costs almost nothing until the day you need it. I do not pick the cheapest plan or the fastest plan. I pick the plan whose price matches the business cost of being down. A shop that loses a thousand dollars a minute buys the hot standby. A dashboard that nobody checks on a Sunday buys the cold backup. The mistake is buying one and expecting the other.
Multi-region means the system runs in two regions at once, and the failover is a traffic shift. It is fast, and it is expensive, because you are paying for two systems to do the work of one. Backup-and-restore means one region runs, and the other region holds copies. It is cheap, and it is slow, because the recovery is a restore, not a switch. I pick multi-region when the RTO is under an hour and the business cannot be down for a morning. I pick backup-and-restore when the RTO is a few hours and the budget is real. The middle ground is a warm standby: the second region has the database running but the app servers off, and the failover is starting the app, not restoring the data. It costs less than full multi-region and recovers faster than a cold restore. Most systems I have built land here, and the drill is what proves the middle ground actually works.
At ten times the data, the full backup is the first thing that breaks. It no longer finishes inside the night, so the schedule slips, and the RPO slips with it. I move to incremental backups with a weekly full, and I split the database so the backup runs per shard instead of per system. The restore test gets slower too, because restoring ten times the data takes ten times as long, and the RTO I promised last year no longer fits. I say that out loud and I renegotiate the RTO before the outage does it for me. Retention also grows teeth at 10x: keeping thirty days of backups for a database ten times bigger is a storage bill that needs its own line in the budget.
At a hundred times the data, I stop thinking about one database and start thinking about a fleet. Backups run per shard, restores run in parallel, and the restore test is a pipeline, not a script. The RPO is met by continuous replication, not by scheduled backups, because no schedule is fast enough at that size. The failover is automated, because a human cannot move a hundred shards by hand in an hour. And the drill changes too: I stop drilling the whole system at once and start drilling one shard, one region, one failure at a time, on a rotating schedule. The goal is the same as at 1x: prove the restore works. The machinery is bigger. The proof is the same.
This is a generic outline, not a copy-paste answer for any one database:
#!/bin/bash
# Nightly restore test: prove the latest backup actually restores.
# Runs in a scratch environment. Never touches production.
set -euo pipefail
BACKUP=$(ls -t /backups/db-*.dump | head -1)
START=$(date +%s)
# 1. Restore the latest backup into a scratch database.
restore_db --target scratch --from "$BACKUP"
# 2. Check the data is there and it is fresh, not just old.
ROWS=$(db_query scratch "SELECT COUNT(*) FROM orders")
NEWEST=$(db_query scratch "SELECT MAX(created_at) FROM orders")
[ "$ROWS" -gt 0 ] || { echo "FAIL: zero rows restored"; exit 1; }
# 3. Time it. The restore time is the truth about the RTO.
END=$(date +%s)
echo "RESTORE_OK rows=$ROWS newest=$NEWEST took=$((END - START))s"
# 4. Report it where the team can see it. A silent pass is a pass
# nobody believes. A failure pages the on-call, like any incident.
Four steps, and the checks are the point, not the commands. The restore has to finish, the rows have to be there, the newest record has to be fresh, and the time has to be written down. If any of those four fails, the job fails loudly. A restore test that cannot fail is a test that proves nothing.
This one stayed with me because the backups were green for a year. Every night, a green tick. Every morning, a report that said protected. And then the day we needed them, they were not there.
A primary database went down in the middle of a working day. Nothing exotic. The storage underneath it failed, and the database would not come back. The team did what the plan said: restore from the latest backup. The restore started, ran for an hour, and failed. The backup chain was broken. An incremental backup from three weeks earlier had never completed, and every backup after it depended on that one link. The full backup from the week before was fine, but it was three weeks of data behind the present, and the incrementals that would have bridged the gap were useless without the missing link. The backup jobs had run green every night, because the job only checked that the backup process started and wrote a file. It never checked that the file could be restored. A year of green ticks, and the only backup that mattered was the one nobody had ever opened.
Three things, and I still do all three. First, automated restore tests on a schedule. Every night, the latest backup is restored to a scratch database, the rows are counted, the newest record is checked, and the time is recorded. A failed restore test pages the on-call, the same as a production alert. Second, the backup job itself changed. It no longer reports success when the process starts. It reports success when the backup file passes a checksum and a spot restore of one table. A backup that cannot be read is not a backup. Third, the chain got a guard. The system now alerts the moment an incremental fails, instead of letting the next twenty incrementals pile up on top of a broken link. And the runbook got a new page: what to do when the restore fails, written while the memory was fresh, not while the system was down. I have not trusted a green backup tick since. I trust the restore test from last night.
This is the kind of question I would ask in an interview, and the kind I ask myself before I sign off on a real DR plan. Take a minute with it before you read my approach.
You run a primary-region database for an online shop. The business sets RTO 1 hour and RPO 15 minutes. Design the disaster recovery for this database. What do you back up, how often, where do the copies live, and how do you prove it works?
I would ask five things before I sized anything. How big is the database, and how fast does it grow? How many writes per minute at peak, and what is one hour of lost orders worth? Is the shop allowed to be read-only during a failover, or must writes keep flowing? Where does the standby live, and who pays for it when nothing is broken? And when was the last restore tested, and how long did it take?
A 200 GB database with 5,000 writes a minute at peak. A full backup nightly, incremental every 15 minutes, copies in a second region. That meets the 15 minute RPO on paper. For the 1 hour RTO, a cold restore will not do: restoring 200 GB takes about 3 hours in my experience, so the plan needs a warm standby in the second region, database running, app servers off, with continuous replication. Failover is starting the app and moving the traffic, about 40 minutes in a drill. Restore tests run monthly on the full backup, and the incremental chain is checked nightly by the automated job. The standby costs about as much as a second small database every month, and the business pays it because one hour of lost orders costs more.
I spend the money on the warm standby because the RTO demands it, and I accept that the standby is idle most of the time. I spend the effort on the restore tests because the RPO is only as good as the last successful restore. I accept that the failover is not instant: DNS and caches take time, and the first hour after a failover is slower than normal. And I test the whole thing with a drill every quarter, because a plan I have never run is a plan I am guessing about.
Saved in this browser, so the Lessons index can show your progress across the course.