# Restore procedure has been tested at least 1 time with documented results.

- **Pattern:** `ab-000920` (`database-design-operations.backup-recovery.restore-tested`)
- **Severity:** medium
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000920
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000920)

## Why it matters

An untested backup is not a backup — it is an assumption. Backup files can be incomplete, corrupt, or require a restore procedure the team has never practiced. ISO 25010 recoverability requires not just that backups exist, but that the restoration process works and can be executed under pressure. A team that discovers their restore procedure is broken during an active outage, while users are affected and stakeholders are demanding updates, has materially worse outcomes than a team that discovered and fixed the same gap during a scheduled test.

## Severity rationale

Medium because an untested restore procedure means the team has unknown recovery capability — the backup may be unusable when needed most, during an active incident.

## Remediation

Write a `scripts/restore.sh` with the exact commands, update it with a "last tested" date, and run a quarterly test restore to a staging environment. Document the result.

```bash
#!/bin/bash
# scripts/restore.sh
# Last tested: 2024-01-15 — restored to staging, verified row counts matched production

BACKUP_FILE=$1
TARGET_URL=$2

echo "Restoring $BACKUP_FILE to $TARGET_URL..."
psql "$TARGET_URL" < "$BACKUP_FILE"

echo "Verifying restore..."
psql "$TARGET_URL" -c \
  "SELECT table_name, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 10;"

echo "Restore complete — verify application functionality before promoting."
```

Schedule a quarterly calendar reminder: restore a recent backup to your staging database, verify record counts and a sample of application flows, then update the "last tested" date in the script and your runbook.

## Detection

- **ID:** `restore-tested`
- **Severity:** `medium`
- **What to look for:** List all restore-related documentation and scripts and look for evidence that a database restore has been performed and documented. Evidence includes: (1) A `scripts/restore.sh` or `scripts/db-restore.md` file describing the restore procedure with commands. (2) A runbook or DEPLOYMENT.md section with restore steps AND a "last tested" date. (3) CI/CD pipeline step that periodically performs a test restore to a staging environment. (4) Comments in deployment documentation referencing a previous restore test. A backup that has never been tested is not a real backup — it might be corrupt, incomplete, or require a process the team doesn't know how to execute under pressure.
- **Pass criteria:** Restore procedure is documented with specific commands. The documentation includes evidence that a restore has been tested (a date, a script with test output, or a CI job). The tested procedure covers the most likely disaster scenario (point-in-time restore or latest snapshot restore).
- **Fail criteria:** Backup exists but no restore procedure is documented. No evidence of ever testing a restore. Restore procedure exists but has "TODO: test this" comments indicating it's never been run.
- **Skip (N/A) when:** No backup strategy exists (covered by backup-strategy-documented check). Or project is in early development with no production data.
- **Detail on fail:** Example: `"Backup strategy is documented (Supabase Pro daily snapshots) but no restore procedure is written and no evidence of a test restore."` or `"scripts/restore.sh exists but has a comment 'TODO: actually test this procedure' — untested backup."`.
- **Remediation:** Document and test the restore procedure:

  ```bash
  #!/bin/bash
  # scripts/restore.sh — Database restore procedure
  # Last tested: 2024-01-15 (restored to staging, verified record counts)

  # Restore from pg_dump backup
  # Usage: ./scripts/restore.sh <backup_file.sql> <target_database_url>

  BACKUP_FILE=$1
  TARGET_URL=$2

  if [ -z "$BACKUP_FILE" ] || [ -z "$TARGET_URL" ]; then
    echo "Usage: $0 <backup_file.sql> <target_database_url>"
    exit 1
  fi

  echo "Restoring from $BACKUP_FILE to $TARGET_URL..."
  psql "$TARGET_URL" < "$BACKUP_FILE"

  # Verify restore — check row counts
  echo "Verifying restore..."
  psql "$TARGET_URL" -c "SELECT table_name, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;"

  echo "Restore complete. Verify the application against the restored database before promoting."
  ```

  Schedule a quarterly restore test: pick a recent backup, restore to a staging/test database, verify row counts and application functionality. Document the result and date in your runbook.

---

## External references

- iso-25010 reliability.recoverability

Taxons: operational-readiness, error-resilience

HTML version: https://auditbuffet.com/patterns/ab-000920