As a SQL Server DBA it is absolutely vital you regularly take backups of the databases you look after. If something goes wrong, a restore is required and there is no (valid) backup, guess who will get the blame? You really don’t want to be sitting in a meeting with fingers pointed at you.
Earlier this year I attended a SQL Skills training course (www.sqlskills.com) and one piece of advice that really stuck with me was that you should think about your restore strategy, not your backup strategy when it comes to disaster recovery planning.
I think this is a great way of approaching disaster recovery, think about it. Do you really want to be restoring hundreds of transaction log backups? How long would that take?
The best way of determining the best recovery strategy is to test restoring the backups taken and seeing if the plan you have put in place meets the RTO and RPO requirements of the system:-
RTO – Recovery Time Objective – What is the acceptable time period to bring the database online?
RPO – Recovery Point Objective – What (if any) is the acceptable data loss in the event of a disaster?
When testing the two questions any DBA should be asking are:-
What will I do if the database becomes corrupt?
What will I do if the server hosting the database fails?
You must be able to recover the database as quickly as possible, with the minimum amount of restores in the event of either the database being corrupted or the server failing. Being able to recover the database with no data loss but taking a week to do so really isn’t going to be very good.
I think there is a danger, with a demanding workload, for DBAs to implement a standard “default” recovery strategy and then not performing any testing to verify its suitability. The only time the plan will get tested is when a live database needs to be restored, a dangerous place to be.