SQL Server › Backup & Recovery

How would you design a comprehensive backup and recovery strategy for a large, business critical database with strict recovery time and recovery point objectives?

Advanced

Answer

You would schedule full backups on a regular cycle such as weekly, differential backups daily to reduce restore time, and frequent transaction log backups, perhaps every five to fifteen minutes, to minimize potential data loss, while also regularly testing full restores on a separate server to verify your backups actually work and meet your required recovery time.
-- Example schedule
-- Full backup: Sunday 1am
-- Differential backup: daily at 1am
-- Transaction log backup: every 15 minutes
Real-world example An online payment processor designs a backup strategy with frequent log backups every five minutes to meet a strict recovery point objective, and regularly practices full restores in a test environment to confirm they can recover quickly during a real emergency.

Common follow-ups: What is the difference between recovery time objective and recovery point objective?;How often should backup restores actually be tested in a real environment?