CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A database administrator is configuring a new database server and needs to set up a robust backup strategy. The business requires the ability to restore the database to any point in time within the last 7 days and has a low tolerance for data loss. Which combination of backups BEST meets these requirements?
- AWeekly full backups and hourly transaction log backups.
- BDaily full backups only.
- CWeekly full backups and daily differential backups.
- DDaily full backups and hourly transaction log backups.
Show answer & explanationAnswer & explanation
Correct answer: D. Daily full backups and hourly transaction log backups.
To achieve point-in-time recovery and a low tolerance for data loss (implying a low RPO), a full backup combined with frequent transaction log backups is essential. Daily full backups provide a baseline, and hourly (or more frequent) transaction log backups ensure that the database can be restored to any specific point in time, minimizing data loss.
Why the other options are wrong
- A. Weekly full backups with hourly transaction log backups would allow point-in-time recovery, but the restore process would be very long, requiring the restoration of one full backup and potentially hundreds of transaction log backups. Daily full backups are more practical for a 7-day window.
- B. Daily full backups only would only allow recovery to the time of the last full backup, resulting in up to 24 hours of data loss, which does not meet the 'any point in time' or 'low tolerance for data loss' criteria.
- C. Weekly full and daily differential backups would allow recovery up to the time of the last differential, but not to an arbitrary point in time between differentials, and the RPO could be up to 24 hours.
Point-in-Time Recovery
The ability to restore a database to its exact state at any specific moment in time, typically achieved by combining full backups with a sequence of transaction log backups.
- Requires the database to be in full recovery mode.
- Achieved by restoring a full backup, then applying all subsequent transaction log backups up to the desired point.
- Frequent log backups minimize the potential for data loss (low RPO).
Memory trick: Full + Logs = Point in time, Diff = Faster, but less granular.