Backup & Recovery
◈ 5 cardsThe four recovery facilities, before/after images, and the scenario→technique decision table that the sample exam’s Part B Q5 grades you on directly.
Databases will be damaged — recovery is the plan for when they are
Database recovery is the set of mechanisms for restoring a database quickly and accurately after loss or damage. A DBMS needs four basic facilities:
- Backup facility — a COPY utility that periodically writes a full (or partial) backup copy as the baseline to restore from.
- Journalizing facility — an audit trail kept in two logs:
- - the transaction log (who did what, when, to which rows), and
- - the database change log (the before and after images of every modified record).
- Checkpoint facility — periodically suspends processing to synchronize files and journals, writing a checkpoint record. Recovery can then restart from the last checkpoint (minutes of work) instead of the last backup (possibly hours).
- Recovery manager — the DBMS module that reads the logs and backups, decides what to undo or redo, applies the right images, and restores a consistent state.
Before images vs. after images — the heart of recovery
- A before image is the copy of a record before a change → used to UNDO (rollback / backward recovery), restoring data as if the transaction never happened.
- An after image is the copy after a change → used to REDO (rollforward / forward recovery), re-applying committed changes onto a backup to bring it forward.
So: aborted transaction → before images (rollback); database destroyed after a backup → after images (rollforward). The logs themselves are the most critical files to protect — lose them and recovery becomes impossible.
The two directions
- Backward recovery (rollback) applies before images to undo unwanted changes and return to an earlier state. Used for an aborted transaction, or incorrect data caught quickly.
- Forward recovery (rollforward) starts from a backup and applies after images to move forward to a recent consistent state. Used for database destruction (and for system failure after restoring from a checkpoint). Rollforward beats restore/rerun because it re-applies results (after images) directly rather than reprocessing every transaction's logic — avoiding the sequencing surprises of rerun (e.g. a withdrawal reprocessed before the deposit that funds it).
Worked example: scenario → technique
This is exactly the sample-exam Part B Q5 shape — match each failure to its recovery:
| Failure scenario | Technique | Why |
|---|---|---|
| Transaction aborts mid-way (deadlock, bad input, crash) | Rollback (before images) | Undo the partial transaction so it never happened. |
| Bad-but-valid data discovered soon after entry | Rollback (before images) | Reverse to the pre-error state while it's still recent. |
| Bad data discovered weeks later, after much processing | Compensating transactions / restart from the checkpoint before the error | Too late for a clean rollback; apply correcting transactions or rebuild forward. |
| System failure, database files intact (power/OS/software) | Roll back in-flight transactions, then rollforward committed work from the last checkpoint (or fail over to a mirror) | Undo the unfinished, redo the finished. |
| Database destruction (disk crash, files unreadable) | Restore from backup + rollforward (after images) — or fail over to a mirror | The current DB is gone; rebuild from backup and re-apply committed changes. |
The overall preference order, fastest/least-downtime first: mirroring → rollback → rollforward → restore/rerun.