SQL Disaster Recovery Considerations

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 :

Considerations

• 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

Restoring Master

• 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)

                                               

Conclusion

• 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

Follow @Jody_WP