Disaster Recovery

Disaster recovery planning starts with two numbers for each system: how much recent work the business can afford to lose and re-enter (the recovery point objective) and how long it can operate without the system (the recovery time objective). An order database and a marketing archive can need different answers, and different budgets.

For SQL Server, the database recovery model sets the limit. Under the simple model, if a failure means restoring from backup, changes made since the latest usable full or differential backup cannot be recovered. Under the full model with regular transaction log backups, a database can be restored to a specific point in time, such as the minute before a bad update ran.

A recovery plan can cover:

• Backup schedule and retention for each database, with copies stored away from the primary server
• Scheduled test restores to a separate server, followed by DBCC CHECKDB. RESTORE VERIFYONLY confirms a backup set is complete and readable, but it does not verify the structure of the data inside it.
• A standby server where downtime is expensive, for example through log shipping. Log shipping does not fail over automatically, so the plan names who brings the secondary online and how applications are repointed.
• A runbook with the order of steps, logins, jobs and connection strings needed on the recovery server

No configuration removes downtime entirely. A rehearsed plan gives an estimate of a failure's impact and records who does what. For how replication and log shipping differ, see Database Replication.