CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 5 of 5 · 4 min read

Exercises

Easy

Easy 1: Distinguish full from incremental backups (15 minutes)

State the difference between a full backup and an incremental backup.

Done when: a full backup copying everything, versus an incremental backup copying only changes since the previous backup, is correctly described.

Hint

A full backup copies the entire database; an incremental backup copies only the changes made since the previous backup.

Easy 2: Name what point-in-time recovery combines (15 minutes)

Name the two things point-in-time recovery combines to reach a specific moment.

Done when: a restored backup and the write-ahead log, replayed forward, are both correctly named.

Hint

A restored backup, plus the write-ahead log replayed forward from that point.

Easy 3: Name why same-disk backup storage fails its purpose (15 minutes)

State why storing a backup on the same disk as the live database defeats its purpose.

Done when: a disk failure destroying the backup along with the data it was meant to protect is correctly described.

Hint

A disk failure would destroy the backup right along with the live data, since both are on the same failed disk, leaving nothing left to recover from.

Medium

Medium 1: Decide the restore order for incremental backups (25 minutes)

A database has a full backup from Sunday, and incremental backups from Monday and Tuesday. Determine the correct order to restore these to reach Tuesday's end state.

Done when: Sunday's full backup, then Monday's incremental, then Tuesday's incremental, applied in that order, is correctly stated.

Hint

Sunday's full backup first, then Monday's incremental backup, then Tuesday's incremental backup, applied in that order.

Medium 2: Use point-in-time recovery to fix a human mistake (25 minutes)

An accidental UPDATE with no WHERE clause runs at 3:00pm, overwriting every row's price column. Describe how point-in-time recovery would be used to fix this.

Done when: restoring the most recent backup, then replaying the log forward only up to just before 3:00pm, excluding the destructive statement, is correctly described.

Hint

Restore the most recent backup, then replay the log forward only up to just before 3:00pm, recovering every legitimate change while excluding the destructive UPDATE.

Medium 3: Decide between crash recovery and backup restore (30 minutes)

A server loses power, but its disk remains completely intact and undamaged. Decide whether this calls for ordinary crash recovery or a backup restore.

Done when: ordinary crash recovery is correctly chosen, since the disk, including the log, survived intact, matching the system-crash classification from this level's first page rather than disk failure.

Hint

Ordinary crash recovery, since the disk, including the write-ahead log, survived intact, which is exactly the system-crash case rather than disk failure.

Hard

Hard 1: Explain why incremental backups require their full chain, not just the latest one (30 minutes)

Explain why restoring from Tuesday's incremental backup alone, without Sunday's full backup and Monday's incremental backup, would not correctly reconstruct the database.

Done when: the explanation correctly notes an incremental backup only records changes since the previous backup, so it is meaningless on its own without the full backup and every incremental backup before it in the chain, applied in the correct order.

Hint

An incremental backup only records what changed since the previous backup, so without the full backup and every prior incremental backup in the chain, there is no base state for those recorded changes to even apply to.

Hard 2: Explain why point-in-time recovery needs the log, not just the backup (35 minutes)

Explain why restoring only the most recent backup, without replaying any log at all, would not be sufficient to recover to a specific moment just before a mistake that happened after that backup was taken.

Done when: the explanation correctly notes the backup alone only restores the database to its own timestamp, with every legitimate change made after the backup, up to just before the mistake, existing only in the write-ahead log, which must be replayed forward to recover that additional, otherwise-lost work.

Hint

The backup alone only reflects the database's state at its own timestamp; every legitimate change made afterward, up to just before the mistake, exists only in the write-ahead log, which must be replayed forward to recover that work rather than losing it.

Hard 3: Design a backup strategy consistent with this level's failure classification (40 minutes)

Using the failure classification from this level's first page, propose a backup strategy, including backup type, frequency, and storage location, that would protect against both disk failure and human error for a business-critical database.

Done when: a reasonable strategy is proposed, such as regular full backups combined with more frequent incremental backups, stored on physically separate media or in a separate location entirely, with point-in-time recovery available via the retained log, correctly addressing both the disk-failure and human-error cases this page covers.

Hint

A reasonable strategy combines regular full backups with more frequent incremental backups, stored on physically separate media or in a different location entirely, retaining the write-ahead log to enable point-in-time recovery, addressing both disk failure, via the separately stored backup, and human error, via point-in-time recovery to just before the mistake.