Skip to content

Database Recovery

1. Introduction to Database Recovery

Database recovery is the process of restoring a database to a correct and consistent state after a failure. Failures can corrupt a database, cause data loss, or make it unavailable. Database recovery ensures data integrity, consistency, and minimal downtime.

Why is Database Recovery Important?

✅ Protects against data loss due to system crashes, power failures, or software errors.
✅ Ensures data consistency using transactions and recovery techniques.
✅ Minimizes downtime to maintain business continuity.
✅ Allows rollback of incorrect or incomplete transactions.

2. Types of Database Failures

1. Transaction Failure

Occurs when a transaction cannot be completed due to:

  • Logical errors (e.g., division by zero, constraint violations).
  • Deadlocks (when two transactions wait for each other to release resources).
  • System crashes during execution.

2. System Crash (Soft Failure)

Happens due to:

  • Operating system crash.
  • Hardware failure (CPU, RAM, Disk failures).
  • Power failure causing unsaved transactions to be lost.

3. Disk Failure (Hard Failure)

Happens due to:

  • Physical damage to the storage device (HDD/SSD failure).
  • Bad sectors causing partial data corruption.

4. Media Failure

Occurs when database storage (disk/tape) is damaged beyond repair.

5. Catastrophic Failure

Happens due to natural disasters (fire, flood, earthquake), cyberattacks, or accidental deletion of data.

3. Database Recovery Techniques

To recover from failures, database management systems (DBMS) use recovery techniques.

1. Backup-Based Recovery

  • Full Backup: Copies the entire database at a specific point in time.
  • Incremental Backup: Stores only changes made since the last backup.
  • Differential Backup: Stores changes since the last full backup.
  • Cold Backup: Taken when the database is offline.
  • Hot Backup: Taken when the database is running (common in real-time systems).

✔ Example:

sqlCopyEditBACKUP DATABASE my_database 
TO DISK = 'D:\backup\my_database.bak'
WITH FORMAT, MEDIANAME = 'SQLServerBackups';

2. Log-Based Recovery (Using Transaction Logs)

DBMS maintains logs of all changes made to the database.

  • Write-Ahead Logging (WAL): Logs are written before changes are applied.
  • Redo Log: Helps reapply committed transactions after a crash.
  • Undo Log: Helps rollback uncommitted transactions after failure.

✔ Example:

sqlCopyEditALTER DATABASE my_database 
SET RECOVERY FULL;

3. Shadow Paging

  • Uses a shadow copy of the database pages instead of logging.
  • The original copy remains unchanged until the transaction is successfully completed.
  • After a crash, the DBMS switches to the last consistent copy.

✔ Example: Used in non-logging databases like some embedded systems.

4. Checkpoint Mechanism

  • A checkpoint is a point where all updates in memory are saved to disk.
  • If a failure occurs, recovery starts from the last checkpoint instead of scanning all logs.

✔ Example:

sqlCopyEditCHECKPOINT;

5. Deferred Update (No Undo, Only Redo)

  • Changes are not applied immediately but stored in logs.
  • If the transaction is committed, the logs are reapplied after recovery.
  • No need to undo uncommitted changes.

6. Immediate Update (Undo + Redo Required)

  • Changes are applied to the database before the transaction commits.
  • If a failure occurs:
    • Undo uncommitted transactions.
    • Redo committed transactions.

7. ARIES (Algorithms for Recovery and Isolation Exploiting Semantics)

A widely used recovery algorithm that uses:

  • Write-Ahead Logging (WAL).
  • Replaying logs (Redo, Undo).
  • Checkpoints to speed up recovery.

✔ Example: Used in IBM DB2, PostgreSQL, MySQL (InnoDB engine).

4. Recovery from Different Failures

Failure TypeRecovery Technique
Transaction FailureUndo uncommitted transactions
System CrashRedo committed transactions using logs
Disk FailureRestore from backup
Media FailureRestore from remote backup
Catastrophic FailureDisaster recovery plan (Backup + Logs + Cloud Replication)

5. Types of Database Recovery

1. Rollback (Undo)

  • Used for uncommitted transactions.
  • Restores the previous state by reversing changes.
    ✔ Example:
sqlCopyEditROLLBACK;

2. Roll-Forward (Redo)

  • Used for committed transactions that were lost due to failure.
  • Reapplies transaction logs to recover data.
    ✔ Example:
sqlCopyEditRESTORE DATABASE my_database FROM DISK = 'D:\backup\my_database.bak' WITH NORECOVERY;

3. Database Restore

  • Uses a backup copy to recover data.
    ✔ Example:
sqlCopyEditRESTORE DATABASE my_database FROM DISK = 'D:\backup\my_database.bak';

6. Disaster Recovery Strategies

  • Data Replication: Copying data to a backup server in real-time.
  • Cloud Backup: Storing database snapshots in cloud storage.
  • Failover Clustering: Using standby servers that take over during failures.
  • Point-in-Time Recovery: Restoring the database to a specific timestamp.

✔ Example: Point-in-Time Recovery

sqlCopyEditRESTORE DATABASE my_database 
FROM DISK = 'D:\backup\my_database.bak' 
WITH STOPAT = '2025-02-24 12:00:00';

7. Conclusion

✅ Database recovery ensures data safety and availability.
✅ Different recovery techniques handle different failures efficiently.
✅ Modern DBMS like MySQL, Oracle, and SQL Server have built-in recovery mechanisms.