Database Forensics and Data Recovery Questions

Requirements / goal: restore cluster (5 nodes) after corruption on 2 nodes, minimize data loss, verify integrity, and return service within 4 hours. Assumptions: cluster uses leader-follower replication with WAL/commit logs, backups (daily + incremental), monitoring/alerting and orchestration tools (K8s or cluster manager), ability to promote replicas.

1. Immediate detection & triage (0–10 min)

• Confirm alert source (monitoring, node errors, storage checksum failures).
• Capture scope: node IDs, shards/partitions affected, last healthy commit timestamps, leader/replica roles.
• Notify on-call, DB owner, SRE, Product lead; open incident ticket with priority and 4-hour SLA.

2. Isolation (10–20 min)

• Immediately isolate corrupted nodes (remove from load balancer, mark as read-only or cordon from cluster) to prevent further writes/read corruption.
• Quiesce any background compactions/maintenance on those nodes.

3. Assessment & decision (20–30 min)

• Determine corruption type (filesystem, block-level, logical row-level). Check checksums, file system logs.
• Identify most recent consistent replica(s) or backups for each affected shard. If majority replicas healthy, prefer replica-based recovery to minimize data loss.

4. Restore path selection

• If replicas hold up-to-date data (replication lag ≤ acceptable RPO): perform replica catch-up and re-synchronization.
• If replicas missing data or logical corruption present: restore affected shards from most recent snapshot + apply WAL/transaction logs to point-in-time up to last good commit.

5. Recovery actions (30–150 min)

• For each affected shard:
  • Promote a healthy follower to replace corrupted node’s role if leader was corrupted.
  • If restoring from snapshot:
    • Provision replacement node(s), attach clean storage.
    • Restore snapshot, then apply WALs/incremental logs to target timestamp.
  • If using replication repair:
    • Trigger full resync/repair procedure (e.g., anti-entropy / rsync / SSTable rebuild depending on DB).
• Start recovery in parallel for different shards to meet 4-hour window.

6. Consistency & integrity verification (concurrent with restores)

• Run checksums (MD5/SHA) over key SSTables/files and compare with healthy replicas.
• Run application-level consistency checks:
  • Row counts per partition, transaction sequence continuity, checksum of transaction histories.
  • Spot-check business invariants (e.g., account balances non-negative, sum of credits == sum of debits).
• Run replay of committed transactions from WAL into read-only staging cluster and verify top-N recent transactions match production indexes.

7. Client failover and service continuity

• Keep load balancer redirecting reads/writes away from isolated nodes.
• If leader(s) replaced, switch leader via controlled failover; ensure client drivers reconnect and use updated endpoint metadata.
• If reduced capacity risks SLA, scale up temporary read replicas or route read-heavy traffic to cache (Redis) where safe.

8. Validation & gradual return to full service (150–210 min)

• Smoke tests: synthetic transactions, end-to-end ingestion to verify writes land and are replicated.
• Run data reconciliation job comparing hot path recent transactions with restored data.
• Once checks pass, reintroduce nodes one-by-one into LB and cluster, monitor errors for 30–60 minutes.

9. Post-recovery steps (after service restored)

• Preserve corrupted node storage for forensic analysis; take full snapshots.
• Rotate credentials/access if needed.
• Perform root-cause analysis, produce incident report with RPO/RTO achieved, gaps, and concrete remediation (improve monitoring, more frequent snapshots, increase replication factor, automated failover runbooks).
• Update runbook with timings and commands used.

Roles & communications

• Incident commander: owns timeline and communication.
• DB owner / data engineer: directs technical restore steps.
• SRE: node provisioning, LB changes, monitoring.
• QA: run verification suites.

Key safeguards to minimize data loss

• Prefer replicas + WAL replay over bare snapshot restore.
• Always apply WALs to snapshot to reach latest consistent point.
• Validate with checksums and business invariants before marking data as trusted.

This plan focuses parallelization of restores, conservative verification, and controlled failover to meet the 4-hour SLA while ensuring integrity.

Immediate steps (preserve evidence & triage)

1. Quiesce activity if possible; take copy of affected database files or a snapshot for offline analysis. Export DBCC CHECKDB output and SQL Error Log. Do not run destructive fixes yet.
2. Identify scope: run DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS to list corruption. Cross-check sys.database_files, consistency errors, and check backups' integrity.

Determine corruption scope

• Map reported allocation errors to database objects using DBCC PAGE and sys.dm_db_database_page_allocations. Identify affected filegroups, file IDs, objects, pages.

Attempt repair options (ordered, non-destructive first)

• Restore from the most recent known-good backup to isolated environment; compare data. If restore possible, prefer restore instead of in-place repair.
• If repair must be attempted on production: try DBCC CHECKDB WITH REPAIR_REBUILD (non-destructive) first. REPAIR_ALLOW_DATA_LOSS is last resort only when backups unavailable and business accepts data loss; document expected data loss and obtain stakeholder approval.

Restore strategy using backups

• Restore last full + differential + log backups to point before corruption. If corruption present in backups, find earlier clean backup. Use piecemeal restore for large DBs if only some filegroups affected.

Communication and governance

• Notify stakeholders (DBA lead, engineering, product, legal) with impact, timelines, and mitigation plan. Maintain incident log, actions taken, and approvals especially if using REPAIR_ALLOW_DATA_LOSS.

When is REPAIR_ALLOW_DATA_LOSS acceptable?

• Only when: no valid backups exist, business accepts potential data loss, and alternate mitigation (reconstruction from sources) impossible or slower than acceptably down window. Always perform on copied DB, export salvageable data, and document lost rows.

Post-incident

• Root cause analysis, test backups, improve backup cadence and validation, consider storage hardware/IO issues, enable corruption alerts, and run regular CHECKDB with appropriate scheduling and off-host copies.