Welcome back to DR drill series!

In the last post (here), we have highlighted a number of ways to setup disaster recovery in SQL server, we will discuss the first one here - Log Shipping.

 Log shipping (LS for short) has been around for many years - since the SQL 2000 days as far as I remember (or even older) and it was one of the common setup for DR. It can provide regular restore of logs to the DR server or delayed restore which might help for different scenarios. I won't go into details on use case or the pros and cons of LS and will only focus on how to perform in DR drills.

  For the sake of discussion, let's assume we have 2 servers setup (Prod and DR) with 1 database setup as LS from Prod to DR. Number of databases setup as LS is not important as the concept will be the same.

Method 1:
  DBA might wait until the Prod server is isolated or powered down, ensure the last transaction log has been applied to the DR database, then force the DR database to be online. This is one of method and I have seen many DBA perform, the downside of that is after the drill is completed, you will need to setup the LS database again. Since you did force the database online, it will break LS and you will need to set every up again. I would suggest only use that if you are facing a real disaster and wants to bring up the database asap.

Method 2:
  This can considered to be a cheating method, meaning we will cheat SQL server to perform what we want it to, here is how:
  Ensure the latest transaction log files has been applied to the DR database, shutdown SQL server and copy out the system databases (most importantly master database) as well as the user database included in this drill. You will need to copy both the mdf, ndf as well as the ldf (the log files) to a temp location on the DR server. Once that is completed, start SQL server and perform what you normally did (just like method 1).
  Once the DR drill has been completed successfully, instead of setup LS from the beginning again (which required from method 1), what we need to do is to shutdown SQL again, replace back the system and user databases from the copy before the drill and start SQL server back. Once the DR SQL server is back online, you will see that all user database is still under LS mode, and waiting for log to be restore, depends on the time taken for the drill, it will start to copy the log files from Prod again (assuming its back) and resume its duty back to normal.

  If you are still performing your drill as described in method 1, do consider using method 2 to save your time of re-do LS after the drill, if you have a number of large databases, it will save you a lot of time for the overall process. In practice, normally business are fine to run DR drill using not the latest transaction of the database, meaning the time for the prepare work (the copy on DR server) can perform a lot earlier before the actual drill starts (eg. few hours before). The post re-configuration will be depends on your I/O speed of the copy and the size of the database(s), but it should always be faster then coping the database from production server again through the network.

  Hope this helps for everyone who uses LS, and we will move to our next technology - Database mirroring in the next post!