SQL Disaster Recovery Plan Simulation

In a real-world scenario, databases are critical assets. A disaster (hardware failure, natural event, cyberattack) can lead to significant data loss and downtime, impacting business operations. This challenge simulates a core aspect of disaster recovery planning in SQL: ensuring data consistency and recoverability through backups and transaction logging.

Problem Description

Your task is to design and simulate a simplified disaster recovery strategy for a relational database. You need to implement mechanisms for taking full backups and applying transaction logs to restore the database to a consistent state after a simulated failure. The goal is to be able to recover data up to the point of the disaster with minimal loss.

Key requirements:

• Full Backups: The system must be able to create complete snapshots of the database at regular intervals.
• Transaction Logging: All data modification operations (INSERT, UPDATE, DELETE) must be recorded in a transaction log.
• Point-in-Time Recovery: Given a full backup and a sequence of transaction log entries, you must be able to reconstruct the database state just before a simulated failure occurred.
• Data Consistency: The restored database must reflect all committed transactions up to the recovery point. Uncommitted transactions should not be present.

Expected behavior:

• You will be provided with an initial database state.
• You will simulate a series of data modification operations.
• You will simulate taking a full backup at a specific point.
• You will simulate more data modification operations after the backup.
• You will simulate a disaster event.
• You will then be required to restore the database from the last full backup and apply subsequent transaction logs to recover the data.

Important edge cases to consider:

• Empty Transaction Log: What happens if the log is empty between a backup and a disaster?
• Multiple Transaction Logs: How are multiple log files handled if they exist?
• Commit/Rollback: How are transactions that were committed before the disaster versus those that were not handled?

Examples

Example 1:

Input:

• Initial Database State:
  • Users table:
    • user_id (INT, PRIMARY KEY), username (VARCHAR)
    • (1, 'alice')
    • (2, 'bob')
• Operations:
  1. INSERT INTO Users (user_id, username) VALUES (3, 'charlie'); (Transaction T1, COMMIT)
  2. UPDATE Users SET username = 'robert' WHERE user_id = 2; (Transaction T2, COMMIT)
  3. Full Backup at this point.
  4. INSERT INTO Users (user_id, username) VALUES (4, 'david'); (Transaction T3, COMMIT)
  5. UPDATE Users SET username = 'alice_updated' WHERE user_id = 1; (Transaction T4, BEGIN)
  6. Disaster Event occurs.

Output:

• Restored Database State (after recovery):
  • Users table:
    • (1, 'alice')
    • (2, 'robert')
    • (3, 'charlie')
    • (4, 'david') Explanation:

The system first applies operations 1 and 2. Then, a full backup is taken. Operations 3 and 4 are applied and committed. Operation 5 begins a transaction (T4) but is not committed before the disaster. Therefore, upon recovery, the database should reflect the state after the full backup plus all committed transactions (1, 2, 3, and 4), but not the uncommitted changes from T4.

Example 2:

Input:

• Initial Database State:
  • Products table:
    • product_id (INT, PRIMARY KEY), product_name (VARCHAR), price (DECIMAL)
    • (101, 'Laptop', 1200.00)
• Operations:
  1. INSERT INTO Products (product_id, product_name, price) VALUES (102, 'Keyboard', 75.50); (Transaction T1, COMMIT)
  2. Full Backup at this point.
  3. DELETE FROM Products WHERE product_id = 101; (Transaction T2, BEGIN)
  4. UPDATE Products SET price = 80.00 WHERE product_id = 102; (Transaction T3, COMMIT)
  5. Disaster Event occurs.

Output:

• Restored Database State (after recovery):
  • Products table:
    • (102, 'Keyboard', 80.00) Explanation:

The system takes a full backup after operation 1. Operation 2 begins a delete, which is then not committed. Operation 3 updates another record and commits. The disaster occurs. Recovery involves restoring from the full backup and applying committed logs. T2's delete is rolled back because it was not committed. T3's update is applied.

Example 3 (Edge Case):

Input:

• Initial Database State:
  • Orders table:
    • order_id (INT, PRIMARY KEY), customer_id (INT)
    • (1001, 501)
• Operations:
  1. Full Backup at this point.
  2. Disaster Event occurs immediately.

Output:

• Restored Database State (after recovery):
  • Orders table:
    • (1001, 501) Explanation:

The full backup captures the initial state. Since no transactions occurred between the backup and the disaster, the restored database should be identical to the initial state captured in the backup.

Constraints

• You will be simulating operations and backups using pseudocode data structures and functions.
• The database schema for each scenario will be provided.
• The transaction log will be represented as a sequence of log entries. Each entry will include an operation type (INSERT, UPDATE, DELETE), the affected table and row(s), and a transaction ID. It will also indicate if the transaction was COMMITTED or ROLLED BACK (or is still in progress at disaster time).
• The number of operations and log entries will be manageable for simulation (e.g., up to 1000 operations/log entries).
• The simulation focuses on correctness of recovery, not on high-performance database operations.

Notes

• Consider how you will represent the database state (e.g., a collection of tables, where each table is a collection of rows).
• Think about how to represent the transaction log. Each entry should ideally capture enough information to reverse or reapply an operation.
• Your recovery logic needs to iterate through the full backup and then the relevant transaction logs in chronological order.
• Pay close attention to distinguishing between committed and uncommitted transactions when applying logs.