Backup & Recovery: Strategies, Techniques & Disaster Handling

TAKEAWAYS:

• Backup types differ by scope (full vs. incremental) and trade-offs (storage vs. recovery speed).
• Recovery models (simple, full, bulk-logged) determine transaction durability and log usage.
• Disaster recovery requires RTO (recovery time) and RPO (recovery point) planning—critical for banks like NMB.
• RMAN automates backup/recovery in Oracle, while MySQL uses mysqldump and binary logs.
• Checkpoints reduce crash recovery time by flushing dirty pages to disk periodically.
• Cloud backups (AWS RDS snapshots) add redundancy but introduce latency and cost trade-offs.

1. Why Backup and Recovery Matter

Databases are the backbone of modern systems. A single corruption or accidental deletion can cripple operations. For example:

• Nepal Stock Exchange (NEPSE) cannot afford a crash during trading hours.
• eSewa loses thousands of rupees per minute if transaction logs are lost.
• Pathao drivers rely on real-time ride data—any downtime means lost earnings.

Key metrics define resilience:

• RPO (Recovery Point Objective): Maximum acceptable data loss (e.g., 15 minutes for a bank).
• RTO (Recovery Time Objective): How fast the system must restore (e.g., 1 hour for Ncell’s customer portal).

2. Backup Strategies: Trade-offs in Scope and Speed

Backups vary by granularity and frequency. Choose based on storage cost vs. recovery speed.

A. Full Backup

• Definition: Copies the entire database at once.
• Pros: Simple, complete recovery.
• Cons: Time-consuming, high storage cost.
• Example: Weekly full backup of Daraz’s order database (100TB) at midnight.

B. Incremental Backup

• Definition: Backs up only changes since the last backup (full or incremental).
• Pros: Fast, low storage.
• Cons: Complex recovery (must restore full + all incrementals in sequence).
• Example: Daily incremental backups for Khalti’s transaction logs (50GB/day).

C. Differential Backup

• Definition: Backs up all changes since the last full backup.
• Pros: Faster recovery than incremental (only 2 backups needed).
• Cons: Storage grows over time.
• Example: Daily differential backups for NTC’s billing system (20GB/day).

flowchart TD
    A["Full Backup\n(Weekly)"] -->|"Changes"| B["Differential\n(Daily)"]
    A -->|"Changes"| C["Incremental\n(Hourly)"]
    B --> D["Recovery:\nFull + Differential"]
    C --> E["Recovery:\nFull + All Incrementals"]

Comparison Table:

3. Recovery Techniques

Recovery depends on the failure type and backup strategy.

stateDiagram-v2
    [*] --> Crash
    Crash --> Checkpoint
    Checkpoint --> RedoLogs: Apply logged transactions
    RedoLogs --> UndoLogs: Rollback incomplete transactions
    UndoLogs --> [*]: Database restored
    state Checkpoint {
        [*] --> FlushDirtyPages: Every 1 hour
        FlushDirtyPages --> [*]
    }

A. Crash Recovery

• Cause: System failure (power outage, OS crash).
• Steps:
  1. Redo all logged transactions since the last checkpoint.
  2. Undo incomplete transactions (using undo logs).
• Example: If Ncell’s database crashes at 3 PM, recovery re-applies logs from the last checkpoint (e.g., 2 PM).

B. Point-in-Time Recovery (PITR)

• Definition: Restore to a specific moment (e.g., before a corrupt update).
• Tools:
  • Oracle: RMAN + archived redo logs.
  • MySQL: mysqlbinlog + binary logs.
• Example: eSewa recovers to 10:30 AM after a buggy update at 11 AM.

C. Disaster Recovery

• Definition: Restore after catastrophic loss (fire, flood, ransomware).
• Steps:
  1. Restore from offsite backups (cloud or tape).
  2. Rebuild infrastructure (servers, network).
• Example: NMB Bank uses AWS cloud backups for DR, ensuring RTO < 4 hours.

4. Backup Tools and Methods

# Oracle RMAN example (incremental backup)
RUN {
  ALLOCATE CHANNEL ch1 TYPE DISK;
  BACKUP INCREMENTAL LEVEL 1
    DATABASE PLUS ARCHIVELOG
    TAG 'daily_incremental';
  RELEASE CHANNEL ch1;
}

5. Real-World Example: Kathmandu Traffic Management

Scenario: A traffic control system database crashes during peak hours (7–9 AM). The system uses:

• Daily full backup (midnight).
• Hourly incremental backups.
• RPO = 15 minutes, RTO = 30 minutes.

Recovery Steps:

1. Restore the full backup (takes 10 minutes).
2. Apply incrementals from 6 AM to 7:45 AM (15 minutes).
3. Replay transaction logs from 7:45 AM to crash time (5 minutes).
4. System is back online in 30 minutes.

Why This Works:

• Incrementals minimize data loss (RPO = 15 min).
• Checkpoints reduce redo time.

6. Disaster Scenarios and Mitigation

7. Cloud Backups: Pros and Cons

Example: Ncell uses AWS S3 for backups.

• Pros:
  • Geographically distributed (redundancy).
  • Pay-as-you-go pricing.
  • Automated versioning.
• Cons:
  • Latency in recovery (network dependency).
  • Cost for large datasets (e.g., Daraz’s 1PB database).
  • Vendor lock-in (e.g., AWS vs. Azure).

erDiagram
    ONPREMISE ||--o{ BACKUP : "stores"
    CLOUD ||--o{ BACKUP : "replicates"
    BACKUP ||--|{ DATABASE : "protects"
    DATABASE }|--|| BACKUP : "depends on"

8. Exam Tip

How This Unit is Tested:

• Theory (30%):
  • Define RPO/RTO, checkpoints, and backup types.
  • Compare incremental vs. differential backups.
• Scenario-Based (40%):
  • Given a failure (e.g., "NEPSE’s database crashes at 2 PM"), describe recovery steps.
  • Calculate storage needs for a backup strategy (e.g., "Daraz’s 500GB DB with 10% daily growth").
• Tool-Specific (30%):
  • Write RMAN commands for a backup.
  • Explain how MySQL binary logs enable PITR.

Common Pitfalls:

• Confusing incremental vs. differential (draw the flowchart!).
• Forgetting undo logs in crash recovery.
• Ignoring offsite backups for disaster recovery.

In the Real World

1. eSewa’s Transaction Logs

   • Idea Used: Point-in-Time Recovery (PITR) with binary logs.
   • How: If a bug corrupts transactions at 3 PM, eSewa restores from the last good checkpoint (e.g., 2:45 PM) and replays logs up to 3:00 PM, ensuring no customer loses money.

2. Ncell’s Billing System

   • Idea Used: Incremental Backups + RAID 10.
   • How: Ncell takes hourly incremental backups of call logs and stores them on RAID 10 arrays. If a disk fails, RAID 10 rebuilds without data loss, while incrementals ensure minimal downtime during recovery.

3. Daraz’s Order Fulfillment Database

   • Idea Used: Differential Backups + Cloud Replication.
   • How: Daraz runs daily differential backups (changes since the last full backup) and replicates critical data to AWS in real time. During Black Friday, if a server fails, the cloud replica takes over within 20 minutes (RTO), and differential backups limit data loss to the last 24 hours (RPO).