In this post I hope to provide better insight into what you need to consider when planning your DR strategy.I often find that organizations never have a sufficient DR plan especially around their databases. I have seen organizations take a week or more to restore their primary database due to insufficient planning. What made it worse was that their SLA stated that their vendor could restore any database within a 3 month window.
This annoyed the crap out of me to put it mildly. Luckily it wasn't a SQL database but I felt bad for the support guys . I recently presented at SQLSaturday88 in Cape Town ,South Africa on the importance of planning your DR strategy from a DBA point of view . This is what I recommend :
- Define your systems e.g. Tier1,Tier2 ,Tier3. The reason for this is so that you know which databases you will restore first.
- Decide early on which method you going to use for your restores , are you going to restore directly from tape,disk or will you be using third party software
- Consider if you need point-in-time recovery. Most hardware vendors have replication software that allows you to copy the mdf and ldf files to a point in time
Restoring Directly from Tape
- This part you will require your backup administrators help,the reason for this is so he can ensure that the backups are loaded in the order you want to restore them.
- Depending on which backup software you using e.g. Netbackup,Backup Exec,Tivoli TSM, you will be using their respective UI's,remember the same SQL restore principles apply
Restoring Using 3rd Party Software
- I love these,3rd party replication software,they rock and cut your restore times down to minutes instead of hours (Last time I used it to restore a system that normally takes 8 hours to restore,with the RM software it took 15 minutes) e.g EMC replication manager,HP DRM
- Consider if you need point in time recovery.Most Hardware vendors have replication software that allows you to copy the MDF and ldf files to a point in time.
The Power of Documentation
- Keep your database documentation updated.Your documentation can help you recover your database structures in the event that you have no backups
- Once again make your life easy 3rd party software, RED-GATE's SQLDOC or PragmaticWorks BI Documenter,The database edition is free.
- They usually document all DB schemas,objects ,permissions and include samples of your data
- Do NOT under estimate the power of your documentation in worse case scenario when you have no backups,no tapes this will be your only tool
- Update it at least once a week depending on your database structure changes
When Crap Hits the fan and you have to restore the master database
- This should be your last resort and should only be done when you
- ONLY if you don't know have any idea of which databases you need to restore.
- To do this you need to put SQL in single user mode to do this go start --> programs-->Microsoft SQL Server 2008 R2-->Configuration tools-->SQL Server Configuration Manager
- Once in the SQL config manager right click on properties
Now click on the advanced tab and go down to start-up parameters , go to the end of the line and add a semicolon (;) , then add -m
- Click on apply and then ok and in SQL Config manager restart the server
- Now open the command line and type in the following,Before you do this make sure that the SQL Agent on the server is stopped.
- Once the database is restored,go back into SQL Config manager ,right click,properties ,Advanced,start-up parameters and remove -m,click apply then ok and restart the SQL service
- Open SQL Management studio and you should see the following
before you restore you user databases have a look under security, logins,you will see that all your logins are there(saves you from having to scripting your logins)
- You can now restore your model(if you have customized it) and MSDB database
- After restoring the MSDB database(This will restore all your jobs)
- With restore methods 1&2 you will need to script out you logins,make your life easy,There is a tool call SQLPermissions from Idera Software and run the script(s) after the restores,best of all it's free
- If you have any questions feel free to ask or even better send me feedback Email Jody or on Twitter