Reversing Log Shipping Roles (SQL Server 2005)

Once you failover to the standby database you might want to reverse the roles and use the server which formerly was the primary server as the target of log shipping. This makes sense if you use log shipping for planned failovers while performing maintenance on the primary server. During the maintenance window users can continue submitting read-write queries to the standby server, which will act as the primary server. Subsequently once primary server becomes available you can reverse roles once again and revert to the original configuration. 



Steps to reverse log shipping roles are:
1.     Backup the active portion of the transaction log on the PRIMARY server using NORECOVERY option. This will make the primary database unavailable for any user activity; using NORECOVERY option will also enable you to subsequently restore transaction log backups to the (formerly) PRIMARY database. 
 

2.     Apply all remaining transaction log backups to the STANDBY database. All backups except the last one should be restore using WITH NORECOVERY option. The last backup should be resting using WITH RECOVERY option this will bring the STANDBY database online. 
 

3.     Transfer logins, resolve any inconsistencies between sysusers and sys.syslogins tables and perform any miscellaneous steps for ensuring proper functionality for your applications. Click here to learn more about transferring necessary metadata. 
 

4.     Disable log shipping backup job on the PRIMARY server. Disable log shipping copy and restore jobs on the STANDBY server. 
 

5.    Use database properties' dialog (or Transact-SQL scripts) to configure log shipping from STANDBY to PRIMARY server. Use the same share for storing backups as with initial log shipping configuration. Choose (formerly) PRIMARY database as the target of log shipping.