Microsoft SQL Server 2012 Bible

Switching Roles

Log shipping enables the capability to manually switch roles. This action can be performed for maintenance or in the case of a disaster. Depending on the cause of the disaster and its severity, the likely first step is to make a backup of the active transaction log, or tail of the log, on the primary server (if it is still accessible) with the NORECOVERY option.

The next step is to transfer all the transaction log backups to the secondary server either using a SQL Server Agent job or copying them manually. After copying, the transaction log backups need to be restored in sequential order to the secondary server using the WITH NORECOVERY or WITH STANDBY option for all transaction logs except the last one. The last transaction log is restored using the WITH RECOVERY option to close any open transactions and bring the secondary database up in an online state. If all the transaction log backups have been restored using the WITH NORECOVERY or WITH STANDBY option, there is no need to panic. You can still recover the database using the RESTORE DATABASE <database_name> WITH RECOVERY command. Then disable the log shipping jobs and alerts on the servers participating in the log shipping configuration.

Next verify that any database objects such as logins, jobs, maintenance plans, SSIS packages, and linked servers that reside outside the log shipping database are created on the warm standby server. For example, if the logins are not created on the warm standby server, then ...