Failing Over to a Log Shipping Secondary
Failing over to a log shipping secondary is useful if the primary server instance fails or requires maintenance.
Preparing for a Controlled Failover
Typically, the primary and secondary databases are unsynchronized, because the primary database continues to be updated after its latest backup job. Also, in some cases, recent transaction log backups not have been copied to the secondary server instances, or some copied log backups might still not have been applied to the secondary database. We recommend that you begin by synchronizing all of the secondary databases with the primary database, if possible.
For information about log shipping jobs, see Understanding Log Shipping.
Failing Over
To fail over to a secondary database:
Copy any uncopied backup files from the backup share to the copy destination folder of each secondary server.
Apply any unapplied transaction log backups in sequence to each secondary database. For more information, see How to: Apply a Transaction Log Backup (Transact-SQL).
If the primary database is accessible, back up the active transaction log and apply the log backup to the secondary databases.
If the original primary server instance is not damaged, back up the tail of the transaction log of the primary database using WITH NORECOVERY. This leaves the database in the restoring state, and eventually you will be able to roll this database forward by applying transaction log backups from the replacement primary database.
For more information, see Working with Transaction Log Backups.After the secondary servers are synchronized, you can fail over to whichever one you prefer by recovering its secondary database and redirecting clients to that server instance. Recovering puts the database into a consistent state and brings it online. For more information, see How to: Recover a Database from a Backup Without Restoring Data (Transact-SQL).
Note
When you make a secondary database available, you should ensure that its metadata is consistent with the metadata of the original primary database. For more information, see Managing Metadata When Making a Database Available on Another Server Instance.
After you have recovered a secondary database, you can reconfigure it to act as a primary database for other secondary databases. For more information, see Changing Roles Between Primary and Secondary Servers.
If no other secondary database is available, see How to: Enable Log Shipping (SQL Server Management Studio) or How to: Enable Log Shipping (Transact-SQL).
See Also
Concepts
Log Shipping Tables and Stored Procedures
Tail-Log Backups