SQL Homework – September 2021 – How good is your recovery plan?Leave a comment
September 2, 2021 by Kenneth Fisher
Last month I had you create a security standards document. This month we are going to document our recovery plan. Yes, recovery plan. There is no point in taking backups if you never plan on recovering them, and viewing the whole process from the recovery point of view will make your life a lot easier when planning.
Regardless, document your recovery plan. I don’t care if you are a DBA or a developer. Make sure you know this part. If it’s not something you work on on a regular basis (or ever) then you might need to do some looking around or ask some questions. That’s fine. This is important for everyone to know, not just the people in charge of it.
Your recovery plan document should include the following:
- What is your RPO? (Recovery Point Objective) If you have multiple instances, applications etc there may be multiple RPOs. Make sure you know the default and the exceptions.
- What is your RTO? (Recovery Time Objective) Same as above about supporting multiple applications etc.
- How are you taking your backups? Are you taking native backups or using a tool?
- When are your backups taken? The full schedule, not just full backups. If you are just taking full backups why?
- How about your systems databases? When are they backed up?
- If you have any type of encryption do you have your certificates backed up somewhere?
- If you are responsible for passwords then I assume you have a password store somewhere right? Where is it and how do you get those passwords?
- Where is everything backed up to? Disk? Tape?
- Where are your backups stored? On a network disk? On a local disk? Are your backup files backed up somewhere else? (Hint, they better be.)
- How long are your backups valid for, and how long do you keep them. I.e. if I want to recover data from last year can I? How about last month?
- Is there anything that I missed? Add it too. (And leave a comment for me too!)
Some additional reading: The 9 letters that get DBAs fired.
Now, here comes the absolutely important part. And you might even get with some co-workers to do this one. It can actually be a lot of fun.
How can your recovery plan break?
Obviously you aren’t going to do anything, but could you? If someone malicious came along and decided to trash your database how would they make it so you couldn’t recover anything? How about an accident? Or a true disaster?
If you’ve been a DBA for any length of time you’ve probably heard horror stories. One of my favorites is the company that was in an area that flooded. They lost their entire server room. Including the safe that held the tapes with the backups on them. (Store your backups off site people!) Come up with a story of your own, what’s the worst thing that you can come up with?
And last but not least, what can you do to mitigate things? Obviously some solutions are going to be too expensive, or too slow. Sometimes a disaster is just that and there isn’t a thing you can do to stop it. But can you at least make it better? I’ll bet you can!
Category: Backups, Documentation, Microsoft SQL Server, SQL Homework, SQLServerPedia Syndication | Tags: backup strategy, backups, disaster recovery, Documentation, Microsoft SQL Server, recovery strategy, SQL Homework