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

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.

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.

#!/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.

• ID: database-design-operations.backup-recovery.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:

  #!/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.