Friday, August 10, 2012

How to Reverse Log Shipping Roles


There are times when you may need to reverse the roles of a primary and standby server.  This is common when you need to patch the primary server and still need to allow users access to the data.  When using database mirroring or SQL clustering as your SQL Server high availability option, reserving the roles of the primary and standby servers is quite easy.  With log shipping, however, it is not as straight forward.

You can always bring the standby database online, create a full backup of it, and restore onto the (previously) primary database to initialize it for log shipping.  However, this approach can be cumbersome especially if the database is large. The following steps will allow you to reverse log shipping roles without the need to initialize the (new standby) database.

  1. Disable the log shipping backup job on the primary server.
  2. On the standby server, run the log shipping copy and restore jobs to restore any remaining transaction log backups.
  3. Disable the log shipping copy and restore jobs on the secondary server.
  4. On the primary server, create on last transaction log backup using the NORECOVERY option.
  5. On the standby server, restore this transaction log backup using the RECOVERY option.
  6. On the standby server (which will now be the primary server), right click on the database and select Properties -> Transaction Log Shipping.  Enable the database to become the primary database and configure the backup and secondary server settings.

The following video demonstrates how to reverse log shipping roles.

1 comment:

  1. Thanks a lot for your explanation of reversing the roles without the need to initialize from a full backup. I will use this method when/if I plan to fail back onto the original primary server. Hope I never need to failover in the first place but it is what it is! Maybe you could extend this post by including procedures using SSIS or something to migrate the syslogins from primary to secondary. I am currently researching this topic.

    ReplyDelete