{"id":7077,"date":"2022-08-25T16:56:17","date_gmt":"2022-08-25T16:56:17","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/dr-drill-with-log-shipping\/"},"modified":"2026-11-03T19:27:46","modified_gmt":"2026-11-03T19:27:46","slug":"dr-drill-with-log-shipping","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/dr-drill-with-log-shipping\/","title":{"rendered":"DR Drill with Log shipping"},"content":{"rendered":"<p><strong>Introduction<\/strong><\/p>\n<p>One common task for every DBA is to make sure that all mission-critical SQL Server instances and the databases within them are available around the clock to keep the business up and running by minimizing or no business disruptions.<br \/>\nWhen your primary location experiences a geographical disaster like an earthquake, flood, or fire the business must be prepared by recovering or resuming the services from a geographically different location.&nbsp;<br \/>\nIn SQL Server databases, Log shipping is one of the oldest methods of providing disaster recovery which is implemented in many organizations where other options may be challenging due to environment, administrative skills, or budget.<br \/>\nWhy do we need a DR Drill? In order to avoid business downtime we always have to maintain a proper DR mechanism and it&#8217;s mandatory to conduct a DR test to verify the application connectivity and the data synchronization state once we restore or recover the database on the DR site. It is a best practice to conduct a DR drill every six months, &nbsp;although it entirely depends on the client&#8217;s requirements.<\/p>\n<p><strong>Let\u2019s get into the detailed steps of the DR drill setup:<\/strong><\/p>\n<p>I have already configured the Log shipping and below are the configuration details :&nbsp;<\/p>\n<ul>\n<li>Primary Database Name : <strong>LS_P<\/strong><\/li>\n<li>LS Primary Server : <strong>Node1<\/strong><\/li>\n<li>LS Secondary\/DR Server : <strong>Node2<\/strong><\/li>\n<li>Secondary Database: <strong>LS_DR<\/strong><\/li>\n<\/ul>\n<p><strong>Please follow the checklist for a smooth DR exercise and get the steps prepared for the failover and failback.<\/strong><\/p>\n<p><strong>Checklist<\/strong><\/p>\n<ol>\n<li>Coordinate with the client for the maintenance window to perform the DR drill.<\/li>\n<li>Engage the application team for the connectivity and the data validation during the DR test.&nbsp;<\/li>\n<li>Confirm with the customer if they want to copy any application-related SQL agent jobs on DR server.<\/li>\n<li>Copy all the Logins using `revlogin` script.<\/li>\n<\/ol>\n<p><strong>Steps for Failover<\/strong><\/p>\n<p>1. Take the confirmation from customer to stop their applications from connecting to the LS primary server.&nbsp;<\/p>\n<p>2. Once the customer confirms for go-head, Run Manually LS Backup job on Primary Server and LS Copy, LS Restore on DR server.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture1_10.png\" alt=\"dr drill pic 1\" \/><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture2_9.png\" alt=\"dr drill pic 2\" \/><\/p>\n<p>&nbsp;3. Upon completion of the above jobs, Check the LS report and make sure &nbsp;Last backup, Last copy, and last restore are having the same backup file name.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture3_11.png\" alt=\"dr drill pic 3\" \/><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture4_10.png\" alt=\"dr drill pic 4\" \/><\/p>\n<p>&nbsp;4. On confirming the backup files are same on step#3, Disable all the LS Jobs mentioned on the Primary and DR server.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture5_10.png\" alt=\"dr drill pic 5\" \/><\/p>\n<p>5. Take the Tail log backup on the primary server and this will leave the databases into Restoring state on the primary server.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture7_5.png\" alt=\"dr drill pic 7\" \/><\/p>\n<p>&nbsp;<br \/>\n6. Copy the Tail log backups from primary to DR and Restore with recovery on the DR server, this will bring the database online state.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture8_4.png\" alt=\"dr drill pic 8\" \/><\/p>\n<p>&nbsp;7. Configure Reverse Log shipping from DR to the Primary server.<\/p>\n<p>\n&#8211; From DR server, now configure the Log shipping and during the configuration on secondary database settings, select \u2018No. The secondary database is initialized\u2019 Instead of creating the secondary database from scratch and choose the secondary database from the drop-down.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture9_4.png\" alt=\"dr drill pic 9\" \/><\/p>\n<p>8. Validate the Reverse Log shipping health by manually running the newly created LS backup jobs on the DR server and LS copy and restore jobs on the Primary server.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture10_3.png\" alt=\"dr drill pic 10\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture11_3.png\" alt=\"dr drill pic 11\" \/><\/p>\n<p>9. Check the LS report and make sure &nbsp;Last backup, Last copy, and last restore are having the same backup file name.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture12_3.png\" alt=\"dr drill pic 12\" \/><\/p>\n<p>10. Copy any application-related Jobs, logins from the primary server to DR and fix any orphan users.<\/p>\n<p>11. Confirm to the customer that Failover has been completed and they are good for application testing.<\/p>\n<p><strong>Steps for Failback<\/strong><\/p>\n<p>1. Once the customer confirmed that DR test has been completed, proceed with the below steps to failback.&nbsp;<br \/>\n2. Run Manually LS Backup job on DR and LS Copy, LS Restore on Primary server and Check the LS report to make sure Last backup, Last copy, and last restore are having the same backup file name.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture13_2.png\" alt=\"dr drill pic 13\" \/><\/p>\n<p>&nbsp;3. On confirming the backup files are the same on step#2, Disable the LS backup Job on DR and LS copy and restore Jobs on the Primary server.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture14_1.png\" alt=\"dr drill pic 14\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture15.png\" alt=\"dr drill pic 15\" \/><\/p>\n<p>&nbsp;4. Take the Tail log backup on the DR server and this will leave the databases in Restoring state on the DR server.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture16.png\" alt=\"dr drill pic 16\" \/><\/p>\n<p><strong>NOTE<\/strong>: Clear the tail log backups on the folder which were copied during the failover.&nbsp;&nbsp;<\/p>\n<p>5. Copy the Tail log backups from DR to Primary and Restore the tail log with recovery on the Primary server.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture17.png\" alt=\"dr drill 17\" \/><\/p>\n<p><strong>NOTE<\/strong>: Before copying clear all the tail log backups on the local path of the Primary server which were taken during the failover.<\/p>\n<p>\n6. Enable and run manually the old LS Backup job on the Primary server and old LS copy and restore jobs on the DR server.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture18.png\" alt=\"dr drill pic 18\" \/><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture19.png\" alt=\"dr drill pic 19\" \/><\/p>\n<p>&nbsp;7. Validate the health check to make sure Log shipping is working fine.<\/p>\n<p>Well, we can see the status of the Log shipping is Good on both Primary and secondary.&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture20.png\" alt=\"dr drill pic 20\" \/><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26247-body-Picture21.png\" alt=\"dr drill pic 21\" \/><\/p>\n<p>&nbsp;8. Confirm to the customer that Failback has been completed and do a final test with the application connectivity to the primary server.<\/p>\n<p><strong>Conclusion:<\/strong><br \/>\nI hope you find this discussion useful when you are planning the DR drill with Log shipping.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This blog demonstrates the detailed steps needed during the DR testing with reverse Log shipping method. <\/p>\n","protected":false},"author":2,"featured_media":7079,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26247","content_type":"blog","created_by":"10130","authored_on":"2023-11-15T17:56:17+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Rachamallu Jayaprakash Reddy, SQL Server DBA, Rackspace Technology","seo_description":"Master disaster recovery testing for SQL Server using reverse Log Shipping. Ensure business continuity and data synchronization.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26247-insights_image-database-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Relational Databases","product":"","publisheddate":"2022-08-25T16:56:17","rxt_solutions":"Databases","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"SQL Server DR Log Shipping Method | Rackspace Technology","field_topics":"","field_slug":"dr-drill-with-log-shipping","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7077","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7077","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7077"}],"version-history":[{"count":2,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7077\/revisions"}],"predecessor-version":[{"id":8894,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7077\/revisions\/8894"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7079"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7077"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7077"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7077"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}