Disaster Recovery: RPO, RTO, and What a Backup Actually Loses

Back up a database, let more writes happen, lose the live copy entirely, then restore — and measure the real RPO and RTO instead of quoting them.

A database, and a backup taken at a point in time

import duckdb, os, shutil, time
from datetime import datetime

DB_PATH, BACKUP_PATH = "warehouse.duckdb", "warehouse_backup.duckdb"

con = duckdb.connect(DB_PATH)
con.execute("create table orders (order_id int, amount decimal(10,2), created_at timestamp)")
con.execute("insert into orders values (1, 25.5, '2026-01-04 09:00:00'), (2, 12.0, '2026-01-04 10:00:00')")
con.close()

backup_time = datetime(2026, 1, 4, 12, 0, 0)
t0 = time.perf_counter()
shutil.copy(DB_PATH, BACKUP_PATH)
backup_duration = time.perf_counter() - t0
backup_size = os.path.getsize(BACKUP_PATH)
print(f"backed up at {backup_time}, {backup_size} bytes, took {backup_duration*1000:.1f}ms")

$ python disaster_recovery.py
== backup: copy the live database file ==
backed up at 2026-01-04 12:00:00, 536576 bytes, took 1.9ms

Writes keep happening after the backup

con = duckdb.connect(DB_PATH)
con.execute("insert into orders values (3, 8.75, '2026-01-04 14:00:00'), (4, 40.0, '2026-01-04 15:30:00')")
con.close()
disaster_time = datetime(2026, 1, 4, 16, 0, 0)
rpo = disaster_time - backup_time
print(f"disaster strikes at {disaster_time} - the live database is lost")
print(f"RPO (data since last backup, now unrecoverable): {rpo}")

== more writes happen after the backup, before disaster strikes ==
disaster strikes at 2026-01-04 16:00:00 - the live database is lost
RPO (data since last backup, now unrecoverable): 4:00:00

Two more orders (3 and 4) landed after the backup ran. RPO is not a policy someone chose in a meeting — it is a direct consequence of the backup schedule: a backup at noon and a disaster at 4pm means exactly four hours of writes are structurally unrecoverable, no matter how the restore itself goes.

The disaster, and the restore

os.remove(DB_PATH)  # the disaster: the live file is gone
t0 = time.perf_counter()
shutil.copy(BACKUP_PATH, DB_PATH)
rto = time.perf_counter() - t0
print(f"RTO (time to restore service): {rto*1000:.1f}ms")

con = duckdb.connect(DB_PATH)
rows = con.execute("select * from orders order by order_id").df()
print(rows.to_string(index=False))
print(f"restored {len(rows)} of the 4 orders that existed before the disaster - "
      f"orders 3 and 4 (created after the backup) are gone for good")

== restore from backup, and measure how long it took ==
RTO (time to restore service): 1.9ms
 order_id  amount          created_at
        1    25.5 2026-01-04 09:00:00
        2    12.0 2026-01-04 10:00:00
restored 2 of the 4 orders that existed before the disaster - orders 3 and 4 (created after the backup) are gone for good

RTO here is essentially the file-copy time — sub-millisecond for a half-megabyte file. That is the honest number for this exact setup, and also the reason RTO estimates from a small test database are misleading: a real production restore involves provisioning replacement infrastructure and moving far more data, where the copy itself is a small fraction of total RTO. What the restore cannot do, at any speed, is bring back orders 3 and 4 — they were never in the backup. Faster restores shrink RTO; they do nothing for RPO.

Practice

RPO (data since last backup, now unrecoverable): 0:00:00

duckdb.IOException: IO Error: The file "warehouse_backup.duckdb" exists, but it is not a valid DuckDB database file!

Next: data platform architecture — how storage, orchestration, governance, and observability fit together as one system.