Checklist: DR Plan Sanity Check

September 4, 2017 by SQLSoldier SQL Server 1 Comment

If a disaster struck tomorrow, are you ready for it? Are you sure you thought of everything? I cannot count the number of times something happened, and I thought that I should have been able to anticipate that. It can be a big benefit to get another viewpoint on your disaster plan. No matter how well you think you’ve covered yourself, someone who thinks about things differently than you do can often see something you don’t.

Here’s a checklist of things you might not have considered. This can be your other set of eyes.

DR Plan Sanity Check

Recovery from Data Loss Table dropped, data deletion, update gone awry, etc
	Databases in full recovery model and log file regularly backed up to support point-in-time restores
	If bulk logged recovery model is used, it is only used when required for specific transactions that need to be minimally logged like bulk inserts
	Sufficient data storage exists to be able to restore a copy of largest database if needed to recover lost data

Recovery from Server/Storage Failure SAN/NAS/DAS failure, server failure, VM host failure, local storage failure, etc
	Database files physically located in different storage systems
	Backups stored remotely from server (in addition to or instead of stored locally)
	Locally stored backups stored on a different physical drive from the database files (don’t want to lose db files and backups at same time)
	Custom server configurations and objects scripted out or redundant on multiple servers

Recovery from Corruption Single page restore, partial restore, index recreation
	Backups and restores use WITH CHECKSUM option
	Logs backed up for partial restore
	CHECKDB run regularly on all databases
	Corruption scenarios practiced by all team members
	Backups regularly tested to verify restorability

Recovery from Localized Disaster Hurricane, earthquake, explosion, power outage, flood, etc
	Data is redundant across multiple data centers in different geographical locations (sites)
	Applications can be re-pointed or redirected to a different site without code changes or dev intervention
	Critical IT staff can perform 100% of duties from a remote location
	Backups stored separate from any data center to ensure access no matter which site affected

Recovery from Widespread Disaster Massive asteroid, super volcano, zombie apocalypse, alien attack, other cataclysms
	Data is redundant across multiple data centers in different continents (sites)
	Failover to DR site is automatic in case no IT staff can connect
	IT staff located in geographically remote locations (different continents)