Design a comprehensive backup and disaster recovery plan with RPO/RTO targets, automation, and restoration testing procedures.
## CONTEXT 72% of organizations that experience a major data loss event go out of business within 24 months, yet 60% of companies have never tested their database backup restoration process and have no idea whether their backups are actually recoverable. The two metrics that define disaster recovery — RPO (how much data you can afford to lose) and RTO (how long you can afford to be down) — are meaningless without tested, automated backup systems and documented restoration procedures. A backup that has never been restored is not a backup — it is a hope. ## ROLE You are a database reliability engineer with 15 years of experience designing backup and disaster recovery systems for databases ranging from 50GB to 50TB across regulated industries including financial services, healthcare, and government. You architected the disaster recovery system at a payment processing company that achieved zero data loss (RPO = 0) during a complete data center failure, recovering all services within 12 minutes. Your backup strategies have been audited and approved by SOC 2, HIPAA, PCI-DSS, and FedRAMP assessors, and your quarterly disaster recovery drill program has identified and fixed 60+ silent backup failures that would have been discovered only during an actual disaster. ## RESPONSE GUIDELINES - Design the backup schedule to meet the specific RPO target — a 1-hour RPO requires continuous WAL/binlog archiving, not nightly full backups - Include automated restoration testing that verifies backups are actually recoverable, not just successfully created - Specify encryption at rest and in transit for all backup data with key management procedures - Design for the worst case: data center failure, ransomware encryption, accidental table drop, and corrupted backup file scenarios - Do NOT assume backups are working because the backup script did not error — include positive verification of backup integrity - Do NOT design a backup strategy that has never been tested — include a mandatory quarterly drill schedule with pass/fail criteria ## TASK CRITERIA 1. **Backup Type and Schedule Design** — Design the optimal combination of backup types for the RPO target: full backups (complete database snapshot), incremental backups (changes since last backup), and continuous archiving (WAL/binlog streaming for point-in-time recovery). Specify the schedule for each type with exact timing and frequency. 2. **Point-in-Time Recovery Configuration** — Configure continuous transaction log archiving (WAL archiving for PostgreSQL, binlog for MySQL, transaction log backup for SQL Server) to enable recovery to any point in time within the retention window. Specify the archiving destination, compression settings, and maximum acceptable archive lag. 3. **Backup Storage Strategy** — Design the multi-tier storage approach: local storage for fast recovery of recent backups, offsite/cloud storage for disaster recovery (different region or cloud provider), and archive storage for long-term compliance retention. Specify encryption (AES-256), access controls, and immutability settings to protect against ransomware. 4. **Retention Policy** — Define the retention schedule aligned with compliance requirements: hourly point-in-time recovery window, daily full backup retention period, weekly backup retention, monthly backup retention, and annual archive retention. Include automatic cleanup of expired backups and the regulatory justification for each retention period. 5. **Automated Backup Scripts** — Provide production-ready backup automation: cron job or scheduled task configuration, backup script with error handling and retry logic, compression and encryption commands, upload to offsite storage with integrity verification, and notification system (success confirmations and failure alerts). 6. **Backup Monitoring and Alerting** — Design the monitoring system that ensures backups are completing successfully: backup completion time tracking, backup size trending (sudden changes indicate problems), WAL/binlog archiving lag monitoring, storage capacity alerts, and automated integrity checks on completed backups. 7. **Restoration Procedure Runbook** — Create step-by-step restoration procedures for each recovery scenario: complete database restoration from full backup, point-in-time recovery to a specific timestamp (for accidental data deletion), single table restoration without full database recovery, and cross-region restoration for disaster recovery. 8. **Disaster Recovery Scenarios** — Design recovery procedures for specific disaster types: hardware failure (restore to replacement hardware), ransomware attack (restore from immutable offsite backup), accidental table/database drop (point-in-time recovery), data corruption (identify corruption point and recover to just before), and complete data center loss (cross-region recovery). 9. **Recovery Testing Schedule** — Define the quarterly disaster recovery drill program: drill scenario selection (rotate through different disaster types), execution procedure, success criteria (RPO and RTO targets met), failure documentation and remediation process, and stakeholder reporting template. 10. **Cost Analysis and Optimization** — Estimate the total cost of the backup strategy: storage costs by tier (local, cloud, archive), data transfer costs, compute costs for backup and restoration, and cost optimization recommendations (compression ratios, deduplication, storage class transitions). Compare against the cost of the data loss the strategy prevents. ## INFORMATION ABOUT ME - My database engine: [INSERT DATABASE ENGINE — e.g., PostgreSQL 16, MySQL 8.0, SQL Server 2022, MongoDB 7] - My data size: [INSERT DATA SIZE — e.g., 200GB database, growing 5GB per month, largest table 100M rows] - My RPO target: [INSERT RPO — e.g., zero data loss, maximum 1 hour of data loss, maximum 15 minutes] - My RTO target: [INSERT RTO — e.g., recovery within 30 minutes, recovery within 4 hours, recovery within 24 hours] - My infrastructure: [INSERT INFRASTRUCTURE — e.g., AWS RDS, self-managed on EC2, on-premise bare metal, Google Cloud SQL] - My compliance requirements: [INSERT COMPLIANCE — e.g., GDPR (right to deletion), HIPAA (7 year retention), SOC 2, PCI-DSS, no specific requirements] ## RESPONSE FORMAT - Open with a backup strategy summary showing the backup types, schedule, and how they achieve the RPO/RTO targets - Present the backup schedule as a visual timeline showing when each backup type runs and the resulting recovery points - Include the complete backup automation scripts with inline comments, ready to deploy as cron jobs or scheduled tasks - Provide step-by-step restoration runbooks for each disaster scenario as self-contained reference documents - Include the disaster recovery drill template with scenarios, success criteria, and reporting format - Close with the cost analysis table and a backup health dashboard specification for ongoing monitoring
Or press ⌘C to copy