Dig Your Hole

During a recent class I overheard a group of students trading database restoration horror stories. I listened on as each story became more horrific and harrowing than the one before. Out of nowhere the declaration, “I’ve never had to restore a database”, rang out silencing the crowd…. Once the initial shock wore off the stillness was broken with the question, “You have never had to what?!”
Just to provide a bit of background before my rant, background… justification, whatever. Having spent the past 21 years in law enforcement, training for the worst case scenario has always been a staple of the job. A co-worker and I often refer to this type of preparation as “digging a hole”. First dig a hole as deep as your vertical reach, then jump in and find a way to get out. Great start right? Now dig a few more inches and repeat the process. Keep going and repeat each step until you are adequately challenged and able to climb out of any hole.
I’m sure that the question “How does this type of practice pertain to restoring a database?” has crossed your mind. The answer is that the best time to restore a database is when you don’t have to and if you are faced with recovery in a production environment the steps involved should be completely reflexive. Immediately after database or instance failure IS NOT the time to look up RESTORE DATABASE in BOL!
Nuf said… Start digging:
Using SSMS
   1. Restore a full back up
   2. Restore a full then differential backup
   3. Restore a full, differential, then transaction log
   4. Backup the transaction log putting the database in recovery, restore the full, differential, transaction log, and tail log backup
   5. Backup the transaction log putting the database in recovery, restore the full, differential, transaction log, and tail log backup to a point in time
Deeper
Using T-SQL
   1. Restore a full back up
   2. Restore a full then differential backup
   3. Restore a full, differential, then transaction log
   4. Backup the transaction log putting the database in recovery, restore the full, differential, transaction log, and tail log backup to a point in time
   5. Do a partial restore
   6. Restore from a database snapshot(enterprise edition)
   7. Restore a specific data page(enterprise edition)
Keep digging
Using sqlcmd
   1. Restore a full back up
   2. Restore a full then differential backup
   3. Restore a full, differential, then transaction log
   4. Backup the transaction log putting the database in recovery, restore the full, differential, transaction log, and tail log backup to a point in time
   5. Do a partial restore
   6. Restore from a database snapshot(enterprise edition)
   7. Restore a specific data page(enterprise edition)
Off to a good start, but system database may need to be restored.
Get in over your head
   1. Restore the master database
   2. Rebuild the master database
     a. Restore the master database
   3. Restore the instance
     a. Restore all system databases
     b. Restore all user databases
   4. Restore the instance
     a. Restore all system databases
     b. Reattach all user databases
     c. Fix any orphaned users
Once you are able to breeze through each scenario don’t lie to yourself and believe that you could calmly and quickly restore your production environment. Keep in mind that if a production database or instance fails then there is a higher degree of difficulty as your users begin screaming through the halls asking, “Are we up yet?!?! Are we up yet?!?!” This means training is not yet complete, rather it is time to dig deeper.
Both family and friends have compared my competitiveness to the snowball fight in Dumb and Dumber. This character trait, (that’s right! It’s a trait not a flaw), coupled with my aforementioned background drives me to challenge myself just a little more than most. Aware that no training in restoration would be complete without some added external stressors I spent quite some time considering how to introduce these influences, short of crashing my production system.
After quite some time and deliberation the answer finally came to me.  When I teach out of state I normally leave late Sunday night and arrive early Monday morning, which gives me 7-8 hours before I have to check in at the training center. I realized that these trips were the ideal opportunity to hone my skills under pressure. While waiting in the airport I fire up my laptop, which holds all my presentations, examples, and scripts, and delete log files, data files, open up the master.mdf with notepad and begin to type my memoirs, and any other means I can think of to trash sql. Once the damage is complete I shut er down, board the plane, and wait until I arrive at the hotel in the wee hours to begin the restoration. PERFECT!! This provides the appropriate pressure having to assure that I will be fully prepared to deliver within hours.
By now some ask, “Are you serious? Do you really do that?” Dude, snowball fight, remember? Yes it is a bit extreme, but always train and prepare for the worst. To date I have NEVER been late to a training center and my laptop has always been good to go and performed flawlessly. I can also affirm that I have had to restore production database(s) and instance(s) and have never been surprised, stumped, stressed, or frustrated. I’m still digging.
Side note:
GO Uruguay!