Backup Restore Drill

What this skill does

Executes an end-to-end backup-restore drill: identify a recent snapshot, restore it to a sandbox database, run row-count and checksum integrity checks against a list of critical tables, and produce a drill report.

Inputs

• db_kind: postgres-rds, mysql-rds, or dump-file.
• source_id: RDS instance identifier or path to a pg_dump / mysqldump file.
• sandbox_id: a sandbox DB instance to restore into (must be empty or restorable-into).
• critical_tables: array of fully-qualified table names whose integrity matters most.
• Cloud credentials in env if RDS.

Steps

1. Find the latest snapshot: aws rds describe-db-snapshots --db-instance-identifier <source_id> --query 'DBSnapshots | sort_by(@, &SnapshotCreateTime) | [-1]'. For dump-file, use the path directly.
2. Restore to sandbox: RDS via aws rds restore-db-instance-from-db-snapshot --db-instance-identifier <sandbox_id> --db-snapshot-identifier <id>. Dump-file via pg_restore -d <sandbox-conn> or mysql < dump.sql.
3. Wait for the sandbox to be available: aws rds wait db-instance-available. For dumps, check the restore command exit code.
4. Connect and run a fixed sanity SQL: SELECT version(), SELECT current_database().
5. For each table in critical_tables: run SELECT count(*) FROM <table> and record. If the source DB is reachable and policy allows, also run the same count on source for comparison.
6. Compute a per-table checksum: Postgres uses SELECT md5(string_agg(t::text, ',' ORDER BY <pk>)) FROM <table> t LIMIT 100000; MySQL uses CHECKSUM TABLE <table>.
7. Sample N (default 10) rows by <pk> from both source and sandbox; diff via JSON-canonical form.
8. Tear down the sandbox: aws rds delete-db-instance --db-instance-identifier <sandbox_id> --skip-final-snapshot.
9. Emit a markdown report.

Output

drill-<date>.md containing: snapshot id and creation time, restore duration, per-table row count and checksum match status, sample row diff (if any), and a final verdict (pass if all critical tables match within tolerance). Plus a JSON sidecar for tracking.

Verification

The drill is its own verification — pass requires actual restore success and integrity match. As a meta-check, schedule the drill weekly and verify pass-rate stays at 100%; a single fail must trigger investigation. For dumps, also verify file checksum matches the producer's published checksum (sha256sum <dump-file>).

Edge cases

• Snapshot encrypted with a KMS key sandbox can't access: pre-share the key or run drill in the same account/region.
• Critical table too large to checksum (>10M rows): use a sampled checksum at fixed PK boundaries and document.
• Source schema migrations between snapshot and "now": expect column-shape differences; checksum diff is acceptable if the migration is known.
• Restore failures due to capacity quota: surface as infrastructure-blocker, not a backup failure.