Recovering the tail end of the transaction log

Knowing a great deal about backup and recovery is key to any DBA’s career. Most of us fully understand the recovery models, recovery point objectives and recovery time objectives. What I have found from speaking at many SQL Saturday’s and User Groups is that most DBA’s do not regularly practice recovery strategies.

It is very important for a DBA to know how to recover their environments which could include recovering the master database, performing a point in time restore or possibly a piece meal restore.

In one of my sessions “Know Backup, Know Recovery” I demonstrate performing a tail log backup after deleting a my data file.  That demo gets a lot of praise, however I decided to take it a step further and demonstrate attaching a transaction log to another database and recovering the tail end of the log. I then perform a restore of my full and tlogs including the new created tail log backup. The audience has always been very impressed with that demo. My favorite response was “holy &%^#, you can do that”. I commented back that yes I can, and now you can too.

As a matter of fact, this is my Lightning talk presentation at the 2013 PASS Summit.  I gave this presentation to my User Group this week and decided that I would record it and upload it to my YouTube channel for the rest of the world to enjoy. With that being said, if you have never seen this performed, take less than 5 minutes out of your busy day and watch the video. “Ultimate Tail Log Backup and Restore”

I hope you never have to perform this in a production environment, but at least prepare yourself and know how to do it.