SQL SERVER DBA, Linux and Azure: How to Reverse Log Shipping Roles in SQL Server

How to Reverse Log Shipping Roles in SQL Server

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.
  • Disable the log shipping backup job on the primary server.
  • On the standby server, run the log shipping copy and restore jobs to restore any remaining transaction log backups.
  • Disable the log shipping copy and restore jobs on the secondary server.
  • On the primary server, create on last transaction log backup using the NORECOVERY option.
  • On the standby server, restore this transaction log backup using the RECOVERY option.
  • 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.

No comments:

Post a Comment