Edit

Fail Over to a Log Shipping Secondary (SQL Server)

  • Article

Applies to: SQL Server

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 have not 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 About Log Shipping (SQL Server).

Failing Over

To fail over to a secondary database:

  1. Copy any uncopied backup files from the backup share to the copy destination folder of each secondary server.

  2. Apply any unapplied transaction log backups in sequence to each secondary database. For more information, see Apply Transaction Log Backups (SQL Server).

  3. If the primary database is accessible back up the active transaction log and apply the log backup to the secondary databases. You may need to set the database to single-user mode to obtain exclusive access before issuing the restore command, and then switch it back to multi-user after the restore completes.

    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 therefore unavailable to users. Eventually you will be able to roll this database forward by applying transaction log backups from the replacement primary database.

    For more information, see Transaction Log Backups (SQL Server).

  4. 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.

    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 Manage Metadata When Making a Database Available on Another Server Instance (SQL Server).

  5. After you have recovered a secondary database, you can reconfigure it to act as a primary database for other secondary databases.

    If no other secondary database is available, see Configure Log Shipping (SQL Server).

Related Tasks

See Also

Log Shipping Tables and Stored Procedures
About Log Shipping (SQL Server)
Tail-Log Backups (SQL Server)