Backup & Recovery

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:

1. Backup facility — a COPY utility that periodically writes a full (or partial) backup copy as the baseline to restore from.
2. Journalizing facility — an audit trail kept in two logs:
3. - the transaction log (who did what, when, to which rows), and
4. - the database change log (the before and after images of every modified record).
5. 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).
6. 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.