A database administrator is evaluating different backup strategies for a critical production database. The database is 500 GB, and daily changes amount to approximately 50 GB. The business requires the ability to restore to any point in time within the last 7 days, with minimal data loss. Which backup strategy best meets these requirements while optimizing storage and recovery time?
- AFull backup weekly, differential backups daily
- BTransaction log backups only
- CFull backups daily
- DFull backup weekly, incremental backups daily
Show answer & explanationAnswer & explanation
Correct answer: D. Full backup weekly, incremental backups daily
Incremental backups, when combined with a weekly full backup, provide the ability to restore to any point in time within the backup retention period. This strategy optimizes storage by only backing up changes since the last incremental or full backup, and recovery involves restoring the last full backup followed by all subsequent incremental backups.
Why the other options are wrong
- A. Differential backups save all changes since the last full backup. Over a week, this can grow larger than incremental backups, and recovery requires restoring the full backup and only the latest differential.
- B. Transaction log backups alone do not provide a full data recovery point without a preceding full or differential backup and are typically used for granular point-in-time recovery in conjunction with other backup types.
- C. Daily full backups consume significant storage and I/O, and while they offer point-in-time recovery, they are less efficient than incremental or differential for daily changes.
Incremental Backup
An incremental backup copies only the data that has changed since the last full, differential, or incremental backup. It is efficient for storage and network bandwidth but requires restoring the full backup and all subsequent incremental backups for full recovery.
- Copies only data changed since the last backup (any type).
- Optimizes storage space and backup time.
- Recovery involves restoring the last full backup and all subsequent incremental backups in sequence.
Memory trick: Full, Diff, Inc: Each a step to recovery, choose wisely for your data's journey.