# Timed database restore drills into an isolated target prove RTO and RPO

> Run a timed restore into an isolated target, prove app boot and critical reads, and record RTO/RPO — not backup checkbox advice.
> By Dave · 2026-09-20
> Source: https://otf-kit.dev/blog/db-backup-restore-drill-production

After you leave a Lovable or Bolt sandbox and own the backend, a backup file sitting in object storage is not a recovery plan. Recovery is a **timed restore drill**: restore into an isolated target, prove the app boots and critical reads succeed, then record measured RTO and RPO. Until that drill passes, the backup is fiction — a checkbox, not evidence.

This post is not “turn on daily backups” advice. It is the ops practice you owe once `$DATABASE_URL` points at infrastructure you control. Pair it with [structured production logs](/blog/production-structured-logging-for-agents) so restore failures are triageable, and with [audit trail events](/blog/audit-trail-events-saas-ops) so who ran the drill is attributable. It is also not API client timeouts ([outbound AI HTTP timeouts](/blog/api-timeouts-retries-ai-backends)), uptime probes, or a kit how-to. The claim is narrow: restore on a schedule into a throwaway target, prove boot + reads, write the clock.

## Why sandbox hosts hide restore risk

Sandbox platforms often advertise automatic snapshots and a restore button in their console. That is useful while the product lives on their host. Export the repo and you inherit schemas, migrations, and connection strings — plus a vague memory that “backups were on.” The first bad migration, dropped table, or region incident is when you learn nobody ever restored *your* dump into a fresh database and pointed a staging app at it.

Official database docs treat backup and restore as related but separate skills. PostgreSQL documents three families — SQL dumps, filesystem-level copies, and continuous archiving with point-in-time recovery — and warns that each has different restore assumptions ([Backup and Restore](https://www.postgresql.org/docs/current/backup.html)). Managed providers similarly restore **to a new instance**, not by overwriting production in place ([Amazon RDS restore from snapshot](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RestoreFromSnapshot.html)). Your drill should mirror that: isolated target first, production last.

![Untested backup file on a shelf versus a timed restore drill into an isolated glass target with stopwatch proof](https://cdn.otf-kit.dev/blog/db-backup-restore-drill-production/inbody1-20260920c.png)

## Define RTO and RPO before you touch a dump

NIST contingency planning treats recovery objectives as outputs of a business impact analysis: how long the organization can tolerate downtime (RTO) and how much data loss it can accept (RPO), then select backup and recovery methods that meet those numbers ([NIST SP 800-34 Rev. 1 overview](https://csrc.nist.gov/pubs/sp/800/34/r1/final); [ITL bulletin on contingency planning](https://csrc.nist.gov/files/pubs/shared/itlb/itlbul2010-07.pdf)). Testing is not optional theater — NIST calls plan testing a critical element that validates components and surfaces deficiencies before a real outage.

Write three numbers for *your* app, not generic “24h”:

1. **RPO target** — max acceptable data loss (for example 15 minutes of committed writes).
2. **RTO target** — max time from “declare restore” to “app serves critical reads.”
3. **Drill cadence** — how often you prove both (weekly for early production; at least after every backup-path change).

If your only backup is a nightly logical dump and you promise a 5-minute RPO, the numbers disagree. Fix the backup method or the promise — do not paper over the gap with a green “backup enabled” badge.

## Prefer an isolated restore target every time

A restore that overwrites the live primary is not a drill; it is an incident. Managed snapshot restores create a **new** DB instance you name separately ([RDS restore from snapshot](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RestoreFromSnapshot.html)). Logical dumps restore into a freshly created empty database. Keep credentials, network ACL, and app config for the drill under `$RESTORE_TARGET_URL` — never your production `$DATABASE_URL`.

Minimum isolation checklist:

- New database name / instance identifier (not the prod name).
- Separate credentials with restore rights only on the drill target.
- App process (or one-shot job) that can boot with `DATABASE_URL=$RESTORE_TARGET_URL`.
- Network path that cannot write back to prod (read-only prod snapshot source is fine; write ACL to prod is not).

Tear the target down after you record evidence. Leftover restore instances become stale secrets and surprise cost.

## Run the timed drill with real commands

Pick one primary path and time it end-to-end. Start the clock when you begin restore; stop when the app health check and critical reads pass.

**Logical dump path** (good for smaller DBs and portable drills). PostgreSQL’s `pg_dump` produces consistent exports; custom format pairs with `pg_restore` for flexible reload ([pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html)):

```bash
# Capture (prod-read credentials; write only to backup object store)
pg_dump "$DATABASE_URL" -Fc -f "/tmp/app-$(date -u +%Y%m%dT%H%M%SZ).dump"
# Upload artifact to $BACKUP_OBJECT_URI (your object-store CLI — path/env only)

# Drill restore into empty target (never prod)
createdb "$RESTORE_DB_NAME"   # or provider equivalent against $RESTORE_TARGET_URL
pg_restore -d "$RESTORE_TARGET_URL" --clean --if-exists "/tmp/drill.dump"
```

**Managed snapshot path** (good when the provider owns physical backups). Restore snapshot → wait until status is available → point the drill app at the new endpoint. RDS notes the instance may still lazy-load storage in the background after `available`; for critical tables, force a full scan so cold pages do not surprise you later ([RDS restore from snapshot](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RestoreFromSnapshot.html)).

**Continuous archive / PITR path** (when RPO is minutes). PostgreSQL continuous archiving restores a base backup, then replays archived WAL to a recovery target — a different procedure than loading a `pg_dump` script ([Continuous Archiving and PITR](https://www.postgresql.org/docs/current/continuous-archiving.html)). If that is your production strategy, the drill must exercise *that* path, including `restore_command` and a stop before “now” so you prove intentional RPO, not only “latest.”

Record wall-clock for: fetch backup → restore complete → migrations check (expect none, or a documented no-op) → app boot → proof queries.

## Prove app boot and critical reads — not just “psql connected”

A green `SELECT 1` is necessary and insufficient. After restore, boot the same binary you ship, with env pointed at `$RESTORE_TARGET_URL`, and run a short proof suite your product owns:

```bash
export DATABASE_URL="$RESTORE_TARGET_URL"
# Start app (or migrate-check + one-shot server) against the restore target only
curl -fsS "$RESTORE_APP_HEALTH_PATH"   # path-only; host from env

# Critical reads — replace with your real invariants
psql "$RESTORE_TARGET_URL" -c "SELECT count(*) FROM users WHERE deleted_at IS NULL;"
psql "$RESTORE_TARGET_URL" -c "SELECT id FROM subscriptions WHERE status = 'active' LIMIT 5;"
```

Fail the drill if: restore errors, schema mismatch the app cannot start with, empty tables that should have rows, or health check fails. Pass only when boot + invariants succeed inside the RTO budget. Log correlation IDs on the drill run the same way you log production requests so [agents can triage](/blog/production-structured-logging-for-agents) a failed restore without spelunking raw provider consoles.

![Backup snapshot capsule to isolated restore target to app boot proof to RTO RPO evidence record](https://cdn.otf-kit.dev/blog/db-backup-restore-drill-production/inbody2-20260920c.png)

## Write RTO/RPO evidence you can cite later

NIST’s contingency process ends with testing, training, and maintenance — plans stay living documents updated when systems change ([ITL bulletin](https://csrc.nist.gov/files/pubs/shared/itlb/itlbul2010-07.pdf)). Your drill report is that maintenance artifact for the database layer. Store it beside the backup object (or in the same ops repo), not in a chat thread:

```text
drill_id: 2026-09-20T15-10Z
backup_object: $BACKUP_OBJECT_URI
backup_taken_at: 2026-09-20T14:00:00Z
restore_started_at: 2026-09-20T15:10:00Z
restore_ready_at: 2026-09-20T15:27:00Z
app_boot_ok_at: 2026-09-20T15:29:00Z
proof_reads: users_count=18422 subscriptions_sample=5
measured_rto_minutes: 19
measured_rpo_minutes: 70   # wall clock from backup_taken_at to incident/drill declare
rto_target_minutes: 30
rpo_target_minutes: 15
result: FAIL_RPO   # backup cadence too coarse for target — fix schedule or method
operator: drill-bot
notes: isolated target only; prod untouched
```

A FAIL that surfaces “nightly dump cannot meet 15-minute RPO” is a successful drill. A PASS with no numbers is not. Keep product docs [citation-ready](/blog/ai-citation-ready-product-docs) when you publish internal SLOs so the team cites the same measured values.

Fold the drill into your [launch checklist](/blog/launch-checklist-ai-built-app) before strangers depend on the data: backup method chosen, isolated restore proven once, RTO/RPO recorded, next drill scheduled. When you want a starting kit that already owns auth, billing, and a real database boundary you can attach this practice to, browse [OTF templates](https://otf-kit.dev/templates) — the restore discipline still lives in your runbooks either way.

Own the clock. An object in `$BACKUP_OBJECT_URI` is inventory; a timed restore into `$RESTORE_TARGET_URL` with boot proof and written RTO/RPO is the recovery plan.

## Sources

- [PostgreSQL Backup and Restore](https://www.postgresql.org/docs/current/backup.html)
- [PostgreSQL Continuous Archiving and PITR](https://www.postgresql.org/docs/current/continuous-archiving.html)
- [PostgreSQL pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html)
- [Amazon RDS: Restoring from a DB snapshot](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RestoreFromSnapshot.html)
- [NIST SP 800-34 Rev. 1 — Contingency Planning Guide](https://csrc.nist.gov/pubs/sp/800/34/r1/final)
- [NIST ITL Bulletin: Contingency Planning for Information Systems (July 2010)](https://csrc.nist.gov/files/pubs/shared/itlb/itlbul2010-07.pdf)