Backup & Recovery: Strategies, Techniques & Disaster Handling

TAKEAWAYS:

• Backup strategies differ by scope (full vs. incremental) and impact recovery time and storage costs.
• Recovery models determine how transactions are logged and affect restore granularity (e.g., simple vs. full).
• Point-in-time recovery uses transaction logs to restore a database to a specific moment, critical for compliance.
• Disaster recovery requires offsite backups, failover testing, and RTO/RPO planning.
• SQL Server’s RESTORE command combines backup files, transaction logs, and NORECOVERY/RECOVERY options.
• Real-world tradeoffs: eSewa’s payment logs need frequent incremental backups, while NEPSE’s end-of-day trades require full backups.

Core Concepts: Why Backup and Recovery Matter

1. The Backup Hierarchy: Full, Differential, and Incremental

Backups are classified by scope and frequency. Each has tradeoffs in storage cost, backup time, and restore complexity.

mindmap
  root((Backup Strategies))
    Full Backup["Full Backup\n• Copies all data\n• Slowest but simplest restore\n• Example: Weekly full backup of Ncell customer database"]
    Differential["Differential Backup\n• Copies changes since last FULL backup\n• Faster than full but slower than incremental\n• Example: Daily differential for Daraz order history"]
    Incremental["Incremental Backup\n• Copies only changes since last backup (full or incremental)\n• Fastest backup but complex restore\n• Example: Hourly incremental for Pathao ride logs"]

Worked Example: Khalti Transaction Logs

• Scenario: Khalti processes 10,000 transactions/day. A corrupt transaction at 3 PM requires recovery.
• Strategy:
  • Full backup: Sunday at midnight (100 GB).
  • Differential: Monday–Friday at 2 AM (avg. 5 GB/day).
  • Incremental: Every 2 hours (avg. 100 MB/incremental).
• Restore Steps:
  1. Load Sunday full backup.
  2. Apply Friday differential.
  3. Apply 1 PM incremental.
  • Time saved: 4 hours vs. restoring 5 daily differentials.

Comparison Table

2. Recovery Models: How SQL Server Logs Transactions

The recovery model dictates how transactions are logged and whether they can be undone. Choose based on durability needs and performance.

classDiagram
    class RecoveryModel {
        +name: String
        +logsTransactions: Boolean
        +allowsPointInTimeRecovery: Boolean
        +supportsDBCC: Boolean
    }
    class Full {
        +logsAllTransactions
        +allowsUndo/Redo
        +supportsPointInTimeRecovery
    }
    class BulkLogged {
        +logsBulkOperationsMinimally
        +fullLogsForNonBulkOps
    }
    class Simple {
        +noTransactionLogging
        +truncatesLogOnCheckpoint
    }
    RecoveryModel <|-- Full
    RecoveryModel <|-- BulkLogged
    RecoveryModel <|-- Simple

Key Differences

Worked Example: NTC Billing System

• Scenario: NTC’s billing database (1 TB) runs in Bulk-Logged mode for monthly subscriber updates.
• Problem: A bulk UPDATE fails mid-execution. Recovery options:
  • Full model: Restore from backup + replay transaction log.
  • Bulk-Logged: Restore backup + minimal log replay (faster but less precise).
  • Simple: No recovery—data loss accepted.

3. Point-in-Time Recovery: Restoring to the Second

Point-in-time recovery (PITR) uses transaction logs to restore a database to a specific datetime. Critical for:

• Compliance (e.g., NEPSE must prove trade data integrity).
• Accidental deletions (e.g., Daraz deleting a customer’s order by mistake).

How It Works

1. Restore the most recent full backup.
2. Apply all differential backups (if used).
3. Apply incremental backups up to the point before the failure.
4. Use RESTORE LOG with STOPAT to roll forward to the desired time.

-- Example: Restore NEPSE database to 3:15 PM on 2024-05-20
RESTORE DATABASE NEPSE_Trades
FROM DISK = 'NEPSE_Full_20240519.bak'
WITH NORECOVERY; -- Keeps DB in restoring state

RESTORE DATABASE NEPSE_Trades
FROM DISK = 'NEPSE_Diff_20240520.bak'
WITH NORECOVERY;

RESTORE LOG NEPSE_Trades
FROM DISK = 'NEPSE_Log_20240520_1400.trn'
WITH RECOVERY, STOPAT = '2024-05-20T15:15:00';

Real-World Tie-In: NEPSE Trade Correction

• Issue: A trader’s sell order was logged at 3:14 PM but executed at 3:16 PM due to a lag.
• Solution: PITR to 3:15 PM to verify the order’s state before the execution.

4. Disaster Recovery: RTO and RPO

Disasters (fire, ransomware, hardware failure) require predefined recovery plans. Two key metrics:

• RTO (Recovery Time Objective): Max time to restore operations (e.g., "Khalti must restore payments in <2 hours").
• RPO (Recovery Point Objective): Max data loss tolerated (e.g., "Ncell allows 15-minute old call logs").

flowchart TD
    A["Disaster Occurs\n(e.g., Daraz server fire)"] --> B["Trigger DR Plan"]
    B --> C["Restore from Offsite Backup\n(RPO: 1 hour)"]
    C --> D["Failover to Secondary Site\n(RTO: 30 mins)"]
    D --> E["Verify Data Integrity\n(Checksums, logs)"]
    E --> F["Notify Stakeholders\n(e.g., NTC customers)"]

DR Strategies for Nepalese Companies

Worked Example: Kathmandu Traffic Route Optimization (Analogy)

• Scenario: Imagine traffic lights as a database. A power outage (disaster) requires:
  • RPO: No more than 5 minutes of lost sensor data (traffic cameras).
  • RTO: Restore traffic flow in <30 minutes.
• Solution:
  • Backup: Incremental logs every 5 minutes to a cloud server.
  • Failover: Switch to a backup controller with preloaded route plans.

5. Backup Techniques: SQL Server Deep Dive

A. Backup Types in SQL Server

pie
    title Backup Types in SQL Server
    "Full Backup" : 40
    "Differential Backup" : 30
    "Incremental Backup" : 10
    "Transaction Log Backup" : 20

B. RESTORE Command Syntax

The RESTORE command combines backup files and transaction logs. Key options:

• NORECOVERY: Leaves DB in restoring state (for chained restores).
• RECOVERY: Completes restore and makes DB available.
• STOPAT: Specifies point-in-time for log restores.

-- Full restore chain for a bank's loan database
RESTORE DATABASE Loan_Records
FROM DISK = 'C:\Backups\Loan_Full_202405.bak'
WITH NORECOVERY, MOVE 'Loan_Data' TO 'D:\MSSQL\Loan_Records.mdf';

RESTORE DATABASE Loan_Records
FROM DISK = 'C:\Backups\Loan_Diff_20240520.bak'
WITH NORECOVERY;

RESTORE LOG Loan_Records
FROM DISK = 'C:\Backups\Loan_Log_20240520.trn'
WITH RECOVERY, STOPAT = '2024-05-20T14:30:00';

C. Transaction Log Backups

• Purpose: Capture all transactions since the last log backup.
• Frequency: Every 1–15 minutes for critical systems (e.g., online banking).
• Command:

  BACKUP LOG NEPSE_Trades
  TO DISK = 'NEPSE_Log_20240520_1400.trn'
  WITH NO_TRUNCATE; -- Keeps log for restore
  

6. Real-World Applications

In the Real World

1. eSewa Payment Logs

   • Idea Used: Incremental backups + transaction log shipping.
   • How: eSewa’s PostgreSQL database takes incremental backups every 10 minutes and ships transaction logs to AWS every 5 minutes. If a payment fails, admins restore to the last good log (RPO: 5 minutes).

2. Ncell Customer Data

   • Idea Used: Differential backups + point-in-time recovery.
   • How: Ncell’s Oracle DB runs differential backups nightly. During a corruption, they restore the full backup + differential, then apply logs up to the incident time (RTO: 2 hours).

3. Daraz Order Processing

   • Idea Used: Full backups + log backups for high availability.
   • How: Daraz’s MongoDB cluster takes full backups weekly and logs every 30 minutes. During a server crash, they failover to a replica and restore from the latest log (RTO: 15 minutes).

7. Hands-On: Restoring a Corrupted Database

Scenario: The Customers table in a bank’s SQL Server DB was accidentally dropped at 2:30 PM. Backups:

• Full backup: 2024-05-20 00:00 (C:\Backups\Bank_Full.bak)
• Differential: 2024-05-20 02:00 (C:\Backups\Bank_Diff.bak)
• Log backups: Every 30 minutes in C:\Backups\Bank_Log_*.trn

Steps:

1. Restore full backup (leaves DB in restoring state):

   RESTORE DATABASE Bank_DB
   FROM DISK = 'C:\Backups\Bank_Full.bak'
   WITH NORECOVERY, REPLACE;
   

2. Restore differential backup:

   RESTORE DATABASE Bank_DB
   FROM DISK = 'C:\Backups\Bank_Diff.bak'
   WITH NORECOVERY;
   

3. Restore logs up to 2:20 PM (just before the drop):

   RESTORE LOG Bank_DB
   FROM DISK = 'C:\Backups\Bank_Log_20240520_1420.trn'
   WITH RECOVERY;
   

4. Verify: Check if the Customers table exists.

8. Common Pitfalls and Best Practices

❌ Mistakes to Avoid

• No backup testing: Assuming backups work until you need them (test restores quarterly).
• Single backup location: Storing all backups onsite (risk of fire/theft).
• Ignoring log backups: Relying only on full/differential backups (data loss between backups).
• Overlooking RTO/RPO: Setting unrealistic recovery times (e.g., "restore in 5 minutes" for a 1 TB DB).

✅ Best Practices

• 3-2-1 Rule: 3 copies, 2 media types, 1 offsite.
• Automate backups: Use SQL Agent jobs or tools like Ola Hallengren’s scripts.
• Monitor backup jobs: Alert on failed backups (e.g., via Nagios or Azure Monitor).
• Document recovery steps: Keep a runbook for each critical DB.
• Compress backups: Reduce storage costs (e.g., WITH COMPRESSION).

9. Exam Tip

How This Unit Is Examined

1. Theory Questions (30%):

   • Define RTO vs. RPO and give an example (e.g., "Ncell’s RPO is 30 minutes—explain").
   • Compare full vs. incremental backups in a table.
   • Explain point-in-time recovery with a SQL RESTORE LOG example.

2. Scenario-Based (40%):

   • Case Study: "The Orders table in Daraz’s DB was corrupted. Backups exist: Full (Sunday), Differential (Monday), Logs (hourly). Write the restore steps to recover orders up to 3 PM."
   • DR Plan: "Design a disaster recovery strategy for a bank with RTO=2 hours and RPO=15 minutes."

3. SQL Commands (30%):

   • Write commands to:
     • Take a differential backup.
     • Restore a DB to a specific time.
     • Set up a transaction log backup job.

Pro Tip:

• Memorize the 3-2-1 rule and RESTORE syntax (especially NORECOVERY vs. RECOVERY).
• Practice with a test DB: Use SQL Server’s ADVENTUREWORKS sample DB to experiment with backups/restores.
• Link to real companies: In exams, tie answers to Nepalese examples (e.g., "Like NEPSE, a trading platform needs PITR for audit trails").