Backup Testing: Validating Your Safety Net

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

                         ┌───────────────┐
                         │  FULL DR      │  ← Annual/Semi-annual
                         │  DRILL        │     Complete regional failover
                         │               │     4-8 hours, high risk
                         └───────┬───────┘
                                 │
                         ┌───────┴───────┐
                         │  SYSTEM       │  ← Quarterly
                         │  RESTORATION  │     Full system to isolated env
                         │               │     2-4 hours, moderate risk
                         └───────┬───────┘
                                 │
                     ┌───────────┴───────────┐
                     │  APPLICATION          │  ← Monthly
                     │  RESTORATION          │     Restore & validate app data
                     │                       │     30-60 minutes, low risk
                     └───────────┬───────────┘
                                 │
               ┌─────────────────┴─────────────────┐
               │  DATABASE/FILE                    │  ← Weekly
               │  RESTORATION                      │     Restore specific datasets
               │                                   │     15-30 minutes, minimal risk
               └─────────────────┬─────────────────┘
                                 │
       ┌─────────────────────────┴─────────────────────────┐
       │  AUTOMATED INTEGRITY                              │  ← Daily/Continuous
       │  CHECKS                                           │     Checksums, row counts
       │                                                   │     Automated, no risk
       └───────────────────────────────────────────────────┘
 
FREQUENCY GUIDELINE:
├── Level 5 (Integrity Checks): Daily/Every backup
├── Level 4 (File/DB Restore): Weekly
├── Level 3 (Application Restore): Monthly
├── Level 2 (System Restore): Quarterly
└── Level 1 (Full DR Drill): Semi-annually minimum

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

# Backup Integrity Verification Script
 
function verify_backup(backup_id):
    results = {
        backup_id: backup_id,
        timestamp: now(),
        checks: []
    }
    
    # Check 1: Backup completion
    backup_metadata = get_backup_metadata(backup_id)
    if backup_metadata.status != "COMPLETED":
        results.checks.append({
            name: "completion_status",
            passed: false,
            detail: f"Backup status: {backup_metadata.status}"
        })
        alert("CRITICAL: Backup incomplete", backup_id)
        return results
    
    results.checks.append({name: "completion_status", passed: true})
    
    # Check 2: Size within expected range
    expected_size = get_historical_average(backup_metadata.source, days=30)
    deviation = abs(backup_metadata.size - expected_size) / expected_size
    
    if deviation > 0.20:  # >20% deviation from average
        results.checks.append({
            name: "size_validation",
            passed: false,
            detail: f"Size {backup_metadata.size} deviates {deviation*100}% from expected"
        })
        alert("WARNING: Backup size anomaly", backup_id)
    else:
        results.checks.append({name: "size_validation", passed: true})
    
    # Check 3: Checksum verification
    stored_checksum = backup_metadata.checksum
    calculated_checksum = calculate_checksum(backup_metadata.location)
    
    if stored_checksum != calculated_checksum:
        results.checks.append({
            name: "checksum_verification",
            passed: false,
            detail: "Checksum mismatch - data corruption detected"
        })
        alert("CRITICAL: Backup corruption", backup_id)
    else:
        results.checks.append({name: "checksum_verification", passed: true})
    
    # Check 4: Decryption test (sample)
    try:
        sample_data = decrypt_sample(backup_metadata.location, key_id=current_key)
        results.checks.append({name: "decryption_test", passed: true})
    except DecryptionError as e:
        results.checks.append({
            name: "decryption_test",
            passed: false,
            detail: f"Decryption failed: {e}"
        })
        alert("CRITICAL: Cannot decrypt backup", backup_id)
    
    # Check 5: For incremental - chain validation
    if backup_metadata.type == "INCREMENTAL":
        chain = get_backup_chain(backup_id)
        for link in chain:
            if not exists(link.location):
                results.checks.append({
                    name: "chain_validation",
                    passed: false,
                    detail: f"Missing chain link: {link.id}"
                })
                alert("CRITICAL: Backup chain broken", backup_id)
                break
        else:
            results.checks.append({name: "chain_validation", passed: true})
    
    # Store results
    save_verification_results(results)
    
    # Update monitoring metrics
    metrics.backup_verification_success.set(
        all(check.passed for check in results.checks)
    )
    
    return results
 
# Run after each backup completes
on_backup_complete(lambda backup_id: verify_backup(backup_id))

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

DATABASE RESTORATION TEST WORKFLOW
═══════════════════════════════════════════════════════════════════
 
PHASE 1: ENVIRONMENT PREPARATION
┌─────────────────────────────────────────────────────────────────┐
│  1. Provision isolated test database instance                   │
│     • Same version as production                                │
│     • Sufficient storage for restored data                      │
│     • Network isolated from production                          │
│                                                                 │
│  2. Retrieve target backup                                      │
│     • Latest backup for routine tests                           │
│     • Randomly selected historical backup for coverage          │
│                                                                 │
│  3. Verify encryption keys are available                        │
└─────────────────────────────────────────────────────────────────┘
 
PHASE 2: RESTORATION EXECUTION
┌─────────────────────────────────────────────────────────────────┐
│  1. Restore database from backup                                │
│     • For PostgreSQL: pg_restore or recovery from WAL           │
│     • For MySQL: mysql < dump.sql or xtrabackup                 │
│     • For MongoDB: mongorestore                                 │
│                                                                 │
│  2. Measure restoration time (contributes to RTO validation)    │
│                                                                 │
│  3. Log any warnings or errors during restoration               │
└─────────────────────────────────────────────────────────────────┘
 
PHASE 3: DATA VALIDATION
┌─────────────────────────────────────────────────────────────────┐
│  1. Compare row counts against production                       │
│     SELECT COUNT(*) FROM each_table                             │
│     Flag if deviation > threshold (e.g., 0.1%)                  │
│                                                                 │
│  2. Verify referential integrity                                │
│     Check foreign key relationships are intact                  │
│                                                                 │
│  3. Validate sample records                                     │
│     Check known records exist with correct values               │
│     Compare latest modified records with production             │
│                                                                 │
│  4. Run application smoke tests                                 │
│     Connect test app instance to restored database              │
│     Execute key queries and operations                          │
│                                                                 │
│  5. Verify index and constraint integrity                       │
│     CHECK TABLE / ANALYZE commands                              │
└─────────────────────────────────────────────────────────────────┘
 
PHASE 4: CLEANUP AND REPORTING
┌─────────────────────────────────────────────────────────────────┐
│  1. Destroy test database instance                              │
│     • Ensure no data leakage                                    │
│     • Reclaim resources                                         │
│                                                                 │
│  2. Record test results                                         │
│     • Restoration time                                          │
│     • Validation results                                        │
│     • Any issues encountered                                    │
│                                                                 │
│  3. Update metrics and dashboards                               │
│                                                                 │
│  4. Alert if any failures                                       │
└─────────────────────────────────────────────────────────────────┘

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

FULL DR DRILL EXECUTION
═══════════════════════════════════════════════════════════════════
 
T-7 days:  Final planning review
           Confirm all participants, distribute runbooks
           Verify DR infrastructure ready
 
T-1 day:   Final go/no-go decision
           Confirm monitoring escalations adjusted
           Customer notification (if applicable)
 
T-0:       DRILL BEGINS - Simulate primary region failure
           ┌─────────────────────────────────────────────────────┐
           │  INCIDENT COMMANDER DECLARES:                       │
           │  "Primary region is down. Initiating DR failover."  │
           └─────────────────────────────────────────────────────┘
 
T+0 to T+5 min:    Detection & Assessment
                   │ Observers note: How quickly identified?
                   │ Who was notified? Communication clear?
 
T+5 to T+20 min:   Decision & Initiation
                   │ Failover decision made and communicated
                   │ DR runbooks initiated
 
T+20 to T+60 min:  Execution
                   │ Database promotion
                   │ Application startup
                   │ Network/DNS cutover
 
T+60 to T+90 min:  Validation
                   │ Smoke tests
                   │ Sample transactions
                   │ Monitoring confirmation
 
T+90 to T+180 min: Extended Operation
                   │ Operate in DR mode
                   │ Process real or simulated traffic
                   │ Monitor for issues
 
T+180 min:         Failback (if in scope)
                   │ Or graceful drill conclusion
                   │ Return to primary operation
 
T+1 day:           Hot Debrief
                   │ Initial findings
                   │ Critical issues identified
 
T+7 days:          Full Retrospective
                   │ Complete analysis
                   │ RTO achieved vs target
                   │ Remediation items assigned
                   │ Next drill improvements
 
SUCCESS CRITERIA EXAMPLE:
├── RTO Target: 60 minutes → Actual: ___ minutes
├── RPO Target: 15 minutes → Actual: ___ minutes data loss
├── All Tier-1 applications operational: □ Yes □ No
├── Customer-facing services available: □ Yes □ No
├── No data corruption detected: □ Yes □ No
├── All critical integrations functional: □ Yes □ No

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

AUTOMATED BACKUP TESTING ARCHITECTURE
═══════════════════════════════════════════════════════════════════
 
┌─────────────────────────────────────────────────────────────────┐
│                     ORCHESTRATION LAYER                          │
│  (Jenkins, Airflow, Temporal, Step Functions)                   │
│                                                                  │
│   ┌──────────────────────────────────────────────────────────┐  │
│   │  Daily Schedule:                                          │  │
│   │  ├── 02:00 - Integrity checks on all new backups         │  │
│   │  ├── 03:00 - Rotate weekly restore test target           │  │
│   │  └── 04:00 - Generate compliance report                  │  │
│   │                                                           │  │
│   │  Weekly Schedule:                                         │  │
│   │  ├── Saturday 02:00 - Full DB restore test (random DB)   │  │
│   │  └── Saturday 06:00 - Application restore test           │  │
│   │                                                           │  │
│   │  Monthly Schedule:                                        │  │
│   │  └── First Saturday - Full system restore simulation     │  │
│   └──────────────────────────────────────────────────────────┘  │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                     EXECUTION LAYER                              │
│                                                                  │
│  ┌───────────────┐  ┌───────────────┐  ┌───────────────┐        │
│  │   Integrity   │  │   Restore     │  │   Validation  │        │
│  │   Workers     │  │   Workers     │  │   Workers     │        │
│  └───────┬───────┘  └───────┬───────┘  └───────┬───────┘        │
│          │                  │                  │                 │
│          ▼                  ▼                  ▼                 │
│  ┌─────────────────────────────────────────────────────────────┐│
│  │  Ephemeral Test Infrastructure                               ││
│  │  ├── On-demand test database instances (RDS, Cloud SQL)     ││
│  │  ├── Isolated VPC with no production connectivity           ││
│  │  ├── Temporary compute for application testing              ││
│  │  └── Automatic cleanup after test completion                ││
│  └─────────────────────────────────────────────────────────────┘│
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                     REPORTING LAYER                              │
│                                                                  │
│  ┌───────────────┐  ┌───────────────┐  ┌───────────────┐        │
│  │   Metrics     │  │   Dashboard   │  │   Alerting    │        │
│  │   (Prometheus)│  │   (Grafana)   │  │   (PagerDuty) │        │
│  └───────────────┘  └───────────────┘  └───────────────┘        │
│                                                                  │
│  Key Metrics Tracked:                                            │
│  ├── last_successful_restore_test_timestamp                      │
│  ├── restore_test_duration_seconds                               │
│  ├── restore_test_success_rate                                   │
│  ├── days_since_last_full_dr_test                               │
│  └── backup_rto_achieved_vs_target                               │
└─────────────────────────────────────────────────────────────────┘