Procedure for Role change in log shipping

We discussed log shipping as a good old DR solution. So when it is in action, there should be an operational procedure in place to do role change quickly and effectively.

I always follow/recommend below steps for performing a role change in log shipping.

Assumption: All the logins and server objects (like linked server, SSIS/DTS etc) in the secondary instance are already in sync with primary instance. This must be an ongoing activity and responsibility of DBA.

Steps:

1. Copy all un-copied backup files from primary server to secondary server. (From where the logs are being applied to secondary instance)

2. If possible and required, backup the tail log of primary database. At this stage I prefer to use “no recovery” clause to ensure that primary database surely goes to an unusable state and be prepared to accept transactional log. Then copy the tail log backup to secondary server. At this point, if primary is online and log shipping is working, you should stop it. (I prefer to remove log shipping from management studio as it is a fast and clean way to perform this operation)

3. Manually apply all the unapplied transactional logs to secondary database to bring the secondary server in synchronization with primary. (If you want to restore upto a certain log file only, you may want to do the same. Technically this is a procedure to restore the transactional log only).

4. Once all the transaction log are applied, bring the secondary database online using below  command:

RESTORE database SECONDARYDATABASE WITH RECOVERY

5. Inform application team to point their applications to secondary database (unless it is automatically handled). At this point, applications are back in business and secondary database is now production. (Primary)

6. After a cup of coffee (J You just recovered from a disaster), make sure you fix the primary instance and then reconfigure log shipping from the scratch.