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?
AdvancedAnswer
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 minutesReal-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.