70-465 · Question #133
You need to recommend a backup process for data warehouse database. The solution must meet the following requirements: - Ensure that if a hardware failure occurs, you can bring the database online…
The correct answer is Set the database to the simple recovery model.; Perform a weekly full backup of the database.; Perform a differential backup of the database every night. Explanation: Data Warehouse Backup Strategy Understanding the Requirements First The three requirements drive every decision: 1. RPO (Recovery Point Objective) ≤ 24 hours - no more than 24 hours of data loss 2. Minimize administrative restore effort - simpler restore chain =…
Question
Exhibit
Answer Area
Drag items
Correct arrangement
- Set the database to the simple recovery model.
- Perform a weekly full backup of the database.
- Perform a differential backup of the database every night.
Explanation
Explanation: Data Warehouse Backup Strategy
Understanding the Requirements First
The three requirements drive every decision:
- RPO (Recovery Point Objective) ≤ 24 hours - no more than 24 hours of data loss
- Minimize administrative restore effort - simpler restore chain = fewer steps
- Minimize transaction log space - logs must not bloat storage
Item-by-Item Analysis
1. Set the database to the simple recovery model
Why first, and why simple (not full):
The recovery model is a foundational setting - it controls how the transaction log behaves and what backup types are available. It must be set before any backup strategy is designed.
Simple recovery model automatically truncates the transaction log at each checkpoint, reclaiming space continuously. This directly satisfies "minimize the space used by transaction logs."
Why not full recovery? Full recovery retains the log until you explicitly back it up, causing log growth. It also enables hourly log backups - but that conflicts with the log-space requirement and adds administrative complexity.
Common misconception: Many assume "full recovery = better." For a data warehouse with an acceptable 24-hour RPO, simple recovery is the correct and intentional choice.
2. Perform a weekly full backup
Why second:
A full backup is the mandatory baseline for any restore chain - you cannot restore a differential without a prior full backup. It must follow the recovery model setting so SQL Server backs up in the correct mode.
Weekly frequency balances storage cost against restore complexity (you never need more than one full backup as a starting point).
3. Perform a differential backup every night
Why third:
A differential captures all changes since the last full backup. Nightly differentials mean your worst-case data loss is ~24 hours - exactly at the RPO limit.
Restore chain is minimal: Full backup -> latest differential only. No log chain to walk through, satisfying "minimize administrative effort."
Common misconception: Some would add hourly log backups here for a tighter RPO. But hourly log backups require full recovery model - incompatible with step 1. In simple recovery, log backups are not possible at all.
Why the Other Items Are Eliminated
| Item | Reason Excluded |
|---|---|
| Set to full recovery model | Logs grow unbounded - violates log-space requirement |
| Hourly transaction log backup | Requires full recovery model; impossible in simple recovery |
| Ship logs to secondary server | Log shipping requires full recovery model - eliminated for the same reason |
| Create a database snapshot | Snapshots depend on the original data files; useless for hardware failure recovery |
Why This Works for a Data Warehouse Specifically
Data warehouses load data in scheduled batch ETL jobs (typically nightly). This means:
- Losing up to 24 hours is acceptable - data can be reloaded from the source system
- Large data volumes make log space a real concern
- Simplicity in restore is valued because DBA resources are often shared
The three-step strategy is clean, low-overhead, and precisely matched to the stated constraints.
Topics
Community Discussion
No community discussion yet for this question.