Database Recovery & Backup: Techniques, Logs, Checkpoints

TAKEAWAYS:

• Backup strategies (full, incremental, differential) determine how quickly and reliably a database can be restored.
• Transaction logs record every change to the database, enabling point-in-time recovery.
• Recovery techniques (immediate update, deferred update) differ in how they handle uncommitted transactions during crashes.
• Checkpoints reduce recovery time by periodically saving the database state and flushing logs to disk.
• ARIES algorithm is a gold-standard recovery protocol that handles concurrency and failures robustly.
• Disaster recovery planning ensures business continuity by combining backups, logs, and failover systems.

Why Recovery and Backup Matter

Databases are the backbone of modern businesses. A single crash or corruption can lead to lost sales, financial penalties, or legal consequences. For example:

• eSewa (Nepal’s digital payment platform) must recover transaction records instantly if a server fails during a festival like Dashain.
• Ncell uses automated backups to restore customer call logs and billing data if a data center outage occurs.
• Daraz relies on real-time transaction logs to refund orders or reprocess payments if a database corruption happens during a sale.

In this unit, we’ll learn how these systems work under the hood.

1. Database Failures: What Can Go Wrong?

Databases face three types of failures:

classDiagram
    class Failure {
        +Types: Transaction, System, Disk
    }
    class TransactionFailure {
        +Aborted due to deadlocks, violations
    }
    class SystemFailure {
        +Crash, power loss, OS failure
    }
    class DiskFailure {
        +Media failure, corruption, head crash
    }
    Failure <|-- TransactionFailure
    Failure <|-- SystemFailure
    Failure <|-- DiskFailure

Real-world example:

• Nepal Stock Exchange (NEPSE) once faced a system crash during trading hours. Without proper recovery, investors would have lost access to their portfolios. Recovery techniques ensured minimal downtime.

2. Backup Strategies: How to Save Your Data

Backups are copies of the database stored separately. The choice of backup strategy affects recovery time (RTO) and data loss (RPO).

Types of Backups

Worked Example: eSewa’s Backup Plan

• Full Backup: Every Sunday at midnight (100GB).
• Differential Backup: Daily at 2 AM (only new transactions, ~5GB).
• Recovery Scenario: If a crash happens on Wednesday, eSewa restores:
  1. Sunday’s full backup.
  2. Monday and Tuesday’s differential backups. RTO: 30 minutes. RPO: 24 hours (no data loss beyond yesterday).

3. Transaction Logs: The Undo/Redo Journal

A transaction log records every SQL operation (INSERT, UPDATE, DELETE) before it’s applied to the database. This log is used for:

• Undo: Revert uncommitted transactions (e.g., if a user aborts a transfer).
• Redo: Reapply committed transactions (e.g., after a crash).

sequenceDiagram
    participant User
    participant DBMS
    participant Log
    participant Database

    User->>DBMS: BEGIN TRANSACTION
    DBMS->>Log: Log "START"
    User->>DBMS: UPDATE account (id=1, balance=1000)
    DBMS->>Log: Log "UPDATE account SET balance=900 WHERE id=1"
    DBMS->>Database: Apply UPDATE
    User->>DBMS: COMMIT
    DBMS->>Log: Log "COMMIT"

Real-world example:

• Khalti logs every transaction (e.g., "Transfer ₹500 from User A to User B"). If a server crashes, Khalti uses the log to:
  • Redo all committed transfers.
  • Undo any incomplete transfers (e.g., if the user pressed "Cancel").

4. Recovery Techniques

Two main approaches exist for handling crashes:

A. Immediate Update (Write-Ahead Logging)

1. Log the transaction before updating the database.
2. Update the database immediately.
3. Commit the transaction (log "COMMIT").

• Pros: Simple, fast for committed transactions.
• Cons: Uncommitted changes may linger in the database during a crash.

B. Deferred Update (Shadow Paging)

1. Log the transaction but do not update the database yet.
2. Create a shadow page (copy of the affected data page).
3. Commit the transaction (log "COMMIT").
4. Swap shadow pages into the database.

• Pros: No dirty pages during crashes.
• Cons: Higher storage overhead, slower writes.

Comparison Table:

5. Checkpoints: Saving the State

A checkpoint is a snapshot of the database where:

• All logged transactions up to that point are committed.
• The log is flushed to disk.
• The database state is saved.

Why?

• Reduces recovery time by minimizing the log to scan.
• Example: A bank runs a checkpoint every 1 hour. If a crash occurs, it only needs to redo transactions since the last checkpoint (not the entire log).

stateDiagram-v2
    [*] --> Active
    Active --> Checkpoint: Every 1 hour
    Checkpoint --> LogFlushed: Write log to disk
    LogFlushed --> DatabaseSaved: Save DB state
    DatabaseSaved --> Active: Resume operations

Real-world example:

• NTC (Nepal Telecom) runs checkpoints every 30 minutes. If a server crashes, recovery takes seconds instead of hours.

6. The ARIES Recovery Algorithm

ARIES (Algorithm for Recovery and Isolation Exploiting Semantics) is the industry standard for recovery. It handles:

1. Analysis: Scans the log to identify:
   • Committed transactions (redo).
   • Uncommitted transactions (undo).
2. Redo: Reapplies committed changes.
3. Undo: Rolls back uncommitted changes.

Steps:

1. Redo Phase: Replay the log from the last checkpoint, redoing all committed transactions.
2. Undo Phase: Scan the log backward, undoing uncommitted transactions.

sequenceDiagram
    participant DBMS
    participant Log
    participant Database

    DBMS->>Log: Scan from last checkpoint
    loop Redo Phase
        Log->>Database: Reapply committed changes
    end
    loop Undo Phase
        Log->>Database: Undo uncommitted changes
    end

Why ARIES?

• Used by Oracle, SQL Server, and PostgreSQL.
• Handles concurrency and failures gracefully.

7. Disaster Recovery Planning

A disaster recovery plan (DRP) ensures business continuity. Key components:

1. Backup Strategy: Full + incremental/differential.
2. Offsite Storage: Backups stored in a different location (e.g., cloud).
3. Failover Systems: Secondary servers ready to take over.
4. Testing: Regular drills to ensure recovery works.

Example: Daraz’s DRP

• Primary Data Center: Kathmandu.
• Secondary DC: Pokhara (mirrored databases).
• RTO: 15 minutes (orders reprocessed automatically).
• RPO: 0 (no data loss via real-time replication).

8. Real Hardware: How Backups Work in Data Centers

Key Components:

• RAID Arrays: Mirror data across disks (e.g., RAID 1).
• Tape Libraries: Store offsite backups (e.g., LTO tapes).
• Cloud Backups: Services like AWS S3 or Google Cloud Storage.

Exam Tip

1. Define recovery vs. backup:
   • Backup = Copying data.
   • Recovery = Restoring data after a failure.
2. Compare backup types (full vs. incremental vs. differential) in tables.
3. Explain ARIES in steps (analysis → redo → undo).
4. Relate to real systems:
   • eSewa → Differential backups.
   • Ncell → Checkpoints.
   • Daraz → Failover systems.
5. SQL queries: If asked to write recovery SQL, use:

   -- Example: Recover a table from backup
   RESTORE DATABASE MyDB FROM DISK = 'C:\Backup\MyDB.bak';
   -- Or rollback a transaction
   ROLLBACK TRANSACTION;
   

Final Note: Database recovery is about prevention (backups) + cure (logs + checkpoints). Master these concepts, and you’ll ace the exam—and keep critical systems like banks and e-commerce platforms running smoothly!