Backups That Actually Restore - Snapshots, PITR, Immutable Copies & Restore Drills

Overview

Nobody cares whether you take backups; they care whether you can restore. Most backup disasters are restore disasters: the job silently failed for months, the backup was in the same region or account that got destroyed, the encryption key was gone, or nobody knew how long a restore takes. A solid backup design combines full snapshots with continuous logs for point-in-time recovery (PITR), keeps at least one copy immutable and isolated from production credentials, follows a retention scheme that balances cost against how far back you might need to go, and is proven by scheduled restore drills with measured timings.

Kinds of backups

Crash-consistent vs application-consistent. A crash-consistent snapshot looks like the power was pulled: databases recover using their write-ahead log, but multi-volume or multi-service state may disagree. Application-consistent backups coordinate with the app (flush, freeze, or use the database's own backup API) so everything lines up.

Point-in-time recovery in miniature (runnable)

# Point-in-time recovery (PITR) in miniature: restore the last base snapshot,
# then replay the write-ahead log (WAL) up to just BEFORE the bad statement.
import copy

snapshot = {"taken_at": 100, "rows": {"u1": "alice", "u2": "bob"}}   # nightly base backup

wal = [  # (timestamp, operation, key, value) archived continuously after the snapshot
    (110, "put", "u3", "carol"),
    (120, "put", "u2", "bobby"),
    (130, "delete_all", None, None),  # the incident: someone ran DELETE without WHERE
    (140, "put", "u9", "zed"),        # writes that landed after the incident
]

def restore(target_ts: int) -> dict:
    db = copy.deepcopy(snapshot["rows"])      # 1. restore base backup into a NEW instance
    for ts, op, key, val in wal:              # 2. replay WAL in order
        if ts > target_ts:                    # 3. stop at the recovery target time
            break
        if op == "put":
            db[key] = val
        elif op == "delete_all":
            db.clear()
    return db

print("restore to t=129 ->", restore(129))   # just before the bad delete
print("restore to t=140 ->", restore(140))   # replaying past it re-applies the damage
# Writes after the incident (u9) are NOT in the t=129 copy: you must reconcile them separately.
lost = {k: v for k, v in restore(140).items() if k not in restore(129)}
print("needs manual reconcile:", lost)

Output:

restore to t=129 -> {'u1': 'alice', 'u2': 'bobby', 'u3': 'carol'}
restore to t=140 -> {'u9': 'zed'}
needs manual reconcile: {'u9': 'zed'}

The last line is the part people forget: restoring to before the incident also throws away legitimate writes made after it. You need a plan to reconcile them, for example by replaying them from an event log or the original API requests.

flowchart TB
  db["Production DB"]
  arch["WAL archive (cross-region, immutable)"]
  snap["Nightly base backup"]
  new["New restored instance"]
  db -->|1. Base backup at 02:00| snap
  db -->|2. Ship WAL segments continuously| arch
  new -->|4. Restore base backup into a NEW instance| snap
  new -->|5. Replay WAL up to 14:30:59| arch

Restore into a new instance, never over the damaged one, so you can compare and you keep evidence.

The 3-2-1 rule and its modern extension

• 3 copies of the data (production plus two backups).
• 2 different storage types or services.
• 1 copy offsite (another region, and ideally another account).
• The modern 3-2-1-1-0 variant adds 1 immutable or offline copy and 0 errors on verified restore tests.

Immutability matters because ransomware and compromised admin credentials go after backups first. Options include object lock in write-once mode (S3 Object Lock in compliance mode, which even the root account cannot shorten), backup vault locks, and a separate backup account whose credentials production systems never hold.

Retention: how far back can you go? (runnable)

// Grandfather-father-son retention: keep 7 dailies, 4 weeklies (Sundays), 12 monthlies (1st of month).
// Shows which backups survive pruning on a given day, and how many copies you pay for.
function daysAgo(today: Date, n: number): Date {
  const d = new Date(today);
  d.setUTCDate(d.getUTCDate() - n);
  return d;
}

function keep(today: Date, backups: Date[]): Set<string> {
  const iso = (d: Date) => d.toISOString().slice(0, 10);
  const sorted = [...backups].sort((a, b) => b.getTime() - a.getTime()); // newest first
  const kept = new Set<string>();
  sorted.slice(0, 7).forEach((d) => kept.add(iso(d)));                        // sons: last 7 days
  sorted.filter((d) => d.getUTCDay() === 0).slice(0, 4).forEach((d) => kept.add(iso(d)));  // fathers
  sorted.filter((d) => d.getUTCDate() === 1).slice(0, 12).forEach((d) => kept.add(iso(d))); // grandfathers
  return kept;
}

const today = new Date(Date.UTC(2026, 9, 6));
const all = Array.from({ length: 400 }, (_, i) => daysAgo(today, i)); // one backup per day for 400 days
const kept = [...keep(today, all)].sort();
console.log(`backups taken: ${all.length}, kept after pruning: ${kept.length}`);
console.log(`oldest kept: ${kept[0]}  newest kept: ${kept[kept.length - 1]}`);
console.log(`a 2-day-old ransomware infection? the 7 dailies still hold clean copies before it`);

Output:

backups taken: 400, kept after pruning: 21
oldest kept: 2025-11-01  newest kept: 2026-10-06
a 2-day-old ransomware infection? the 7 dailies still hold clean copies before it

Press Run. Snippets must be self-contained — no network, files, or native modules.

Grandfather-father-son retention keeps many recent copies and a few old ones. Pick the depth based on how long corruption can go unnoticed: a bug that silently writes bad prices for three weeks needs backups older than three weeks.

Real failures worth knowing

1. GitLab, January 2017: an engineer deleted the primary database directory during an incident. Several of their backup mechanisms turned out not to be working, and they restored from a staging copy about six hours old, losing roughly six hours of data. Their public postmortem is required reading.
2. OVHcloud Strasbourg fire, March 2021: a data center building burned. Customers who stored backups in the same site lost both production and backups.
3. Lost encryption keys: encrypted backups are useless if the key was deleted with the account or region. Back up key material or use multi-region keys, and include key access in drills.

Restore drills

A restore drill is a scheduled, measured exercise:

1. Pick a random backup (not the newest one) and a random target time.
2. Restore into an isolated environment using only the documented runbook.
3. Run integrity checks: row counts, checksums, application smoke tests.
4. Record the elapsed time per step and compare to the RTO.
5. Fix whatever was missing in the runbook, then repeat next quarter.

What happens if you skip these practices

• Backups in the same account: one compromised admin credential deletes production and backups together.
• No PITR: your RPO for logical corruption is the time since the last snapshot, which can be a full day.
• No restore tests: you learn the restore takes 14 hours, or fails on a version mismatch, during the outage.
• Replication treated as backup: the bad delete propagates to every replica within seconds.

Pros and cons

Interview Q&A

Check yourself

Elsewhere in the library

These pages stay as they are. This lesson only points at them: Object storage, Secrets and KMS, Sync, async, and semi-sync replication.

Go Deeper

• PostgreSQL docs: Continuous archiving and point-in-time recovery
• GitLab postmortem of the January 31, 2017 database outage
• Amazon S3 Object Lock
• Google SRE book: Data Integrity