It’s a common question “Do you have a backup?” But it’s the wrong question. Very relevant for this month’s T-SQL Tuesday, hosted by Ken Fisher (@sqlstudent144), on the topic of backups.
I think the question should be “Can you recover if needed?”
We all know that a backup is only as good as your ability to restore from it – that you must test your backups to prove their worth. But there’s more to it than being able to restore a backup. Do you know what to do in case of a disaster? Can you restore what you want to restore? Does that restore get your applications back up? Does your reporting become available again? Do you have everything you need? Are there dependencies on other databases?
I often find that organisations don’t quite have the Disaster Recovery story they need, and this is mostly down to not having practised specific scenarios.
Does your disaster testing include getting applications to point at the new server? Have anything else broken while you do that?
Does your disaster testing include a scenario where a rogue process changed values, but there is newer data that you want to keep?
Does your disaster testing include losing an extract from a source system which does incremental extracts?
Does your disaster testing include a situation where a well-meaning person has taken an extra backup, potentially spoiling differential or log backups?
Does your disaster testing include random scenarios where your team needs to figure out what’s going on and what needs to happen to get everything back?
The usefulness of standard SQL backups for some of these situations isn’t even clear. Many people take regular VM backups, but is that sufficient? Can you get the tail of the log if your VM disappears? Does a replicated copy of your database provide enough of a safety net here, or in general?
The key issue is not whether you have a backup. It’s not even whether you have a restorable backup. It’s whether you have what you need to survive if things go south – whichever southbound route you’ve been taken down.