SQL Backup and Recovery: Preparing for the Worst
Backups that have never been restored are articles of faith. Here is how I design a backup strategy and test recovery so it works when I need it. A backup you have never restored is a hope, not a backup. I learned this after a colleague confidently reported that backups were running, only to discover during an incident that the restore process was broken and the last usable backup was three weeks old. Since then, I treat backup and recovery as a single practice: design the backup, write the recovery runbook, and test it regularly. Here is how I approach both. The 3-2-1 Rule The 3-2-1 rule is the foundation of any backup strategy: keep three copies of your data, on two different media, with one copy off-site. Three copies means the live data plus two backups. Two media means not everything on the same disk or the same cloud. One off-site means a copy that survives a site failure. For a database in the cloud, this typically means: the live database, a daily snapshot stored in the same region, and a snapshot copied to a different region or to cold storage. The off-site copy protects against region-wide…