Warehouse Inventory System: Choosing a Backup Strategy
A mid-sized parts distributor runs an on-premises inventory database. The database is 400 GB, and a full backup takes 5 hours. Transaction logs grow at about 8 GB per day. The business operates 24 hours, and the order desk reports that a 30-minute outage during the day costs roughly 2 million yen in lost and delayed orders. The current plan is a weekly full backup on Sunday night plus nothing else. Last month a disk failure corrupted the database at 14:00 on a Thursday. Recovery required restoring Sunday's full backup, losing four days of orders, and the restore itself took 6 hours. Management asks you to redesign the backup plan. The IT budget allows one additional storage array and no cloud services.
1. Identify the two recovery objectives the current plan fails to meet, and explain why each failure occurred.
Read the worked answer
The two objectives are RPO (Recovery Point Objective) and RTO (Recovery Time Objective).
- RPO = the maximum acceptable amount of data loss. With only a weekly full backup, the worst case is nearly 7 days of lost transactions. The Thursday failure lost 4 days. The log growth of 8 GB/day means those lost orders are reconstructable only from paper traces, if at all.
- RTO = the maximum acceptable downtime. A 6-hour restore plus detection time far exceeds the tolerance implied by 2 million yen per 30 minutes. Every extra hour of downtime multiplies the loss.
The root cause is that the plan addresses only media failure of the whole database and ignores point-in-time recovery. A full backup alone cannot bound either objective.
2. Propose a revised schedule using full and transaction-log backups, and justify the intervals against the stated costs.
Read the worked answer
A workable design:
- Weekly full backup (Sunday night, 5 h) as the base image.
- Daily differential or full backup to shorten restore chains.
- Transaction-log backups every 30 minutes (roughly 8 GB/48 ≈ 170 MB each). This bounds RPO to about 30 minutes, matching the 2 million yen per 30-minute cost figure - spending on log storage is cheaper than the loss it prevents.
- Restore test quarterly to confirm the 6-hour restore can be shortened (e.g., by restoring to the new array in parallel).
Justification: log backups are small relative to the 400 GB base, so the added storage array is sufficient. The 30-minute log interval directly ties the worst-case data loss to the known cost of a 30-minute outage. The restore time must also be measured, because RTO is set by restore speed, not backup frequency.
3. The order desk asks whether they can keep working during a restore. What should you tell them, and what would change the answer?
Read the worked answer
With a single on-premises database and no replication, the answer is no - the database is unavailable while the base image and logs are replayed. The 6-hour restore observed last month is the realistic downtime unless the design changes.
What would change the answer: adding a standby/replica server (the second storage array could host it) so that failover keeps the order desk running while the primary is rebuilt. That converts a restore-time outage into a short failover. Without replication, the honest answer is that the 30-minute cost figure will be exceeded during any full restore, and the business must accept that residual risk or fund a standby.