Does SQL Server log shipping automatically fail over?

No. Its log-shipping configuration does not perform automatic failover.

Is a copied backup already reflected in the secondary's data?

Not until the required restoration has applied it.

Can a secondary be intentionally behind?

Yes. Restoration can be configured with a delay, which should be distinguished from unexpected failure or backlog.

AI Glossary / Data engineering and architecture / Advanced

Log shipping

Definition

Log shipping maintains a secondary database by transferring and applying transaction-log information from a primary database. In SQL Server, the feature automates a sequence of log backup, copying and restoration. It supports a standby arrangement, but the secondary can lag behind the primary. Shipping logs is not the same as automatically switching an application to a fully current replacement database after a failure.

What it means

A database changes continually as transactions occur. Rather than repeatedly replacing a secondary with a new complete copy, log shipping carries successive recorded changes forward. The useful business question is therefore not simply whether another database exists. It is how far that database has progressed and what must happen before people can use it after an interruption. This distinction matters for applications that rely on recent transactions, including AI services whose supporting records change during normal operation. A secondary containing yesterday's or last hour's state may be technically available yet unsuitable for a particular recovery objective.

How it works

Microsoft describes three stages: back up the primary transaction log, copy the backup to a secondary and restore it there. Each secondary applies its own sequence. Monitoring can track the stages and report delays or failures. SQL Server log shipping does not itself provide automatic failover. Depending on configuration, a secondary can offer limited read-only access between restores, and restoration can be deliberately delayed. The design should be evaluated as a pipeline: a successful backup does not prove successful copying, and a copied file does not prove that its changes have reached the secondary database.

Three practical examples

An operations team maintains a standby database and rehearses the steps required to make it usable after the primary becomes unavailable.
A reporting team uses an appropriately configured secondary while clearly displaying the latest data state available to readers.
A recovery exercise checks whether alerts distinguish a failed backup stage from a copy backlog or delayed restoration.

Think of it this way

Imagine a second office maintaining a duplicate register from numbered packets of changes. Preparing a packet, delivering it and entering its changes into the duplicate are separate achievements. A delivered envelope sitting unopened on a desk has not updated the register. The analogy highlights the stages; actual database recovery also depends on the integrity and continuity of the required recorded changes.

Worked example

Consider an original simplified timeline. At 10:20, the primary contains transactions through 10:20. The latest successfully created log backup covers changes through 10:15. The secondary has copied that backup, but has applied changes only through 10:10. Under these stipulated coverage times, the secondary's applied state trails the primary by ten minutes. Five minutes of changes, from 10:10 to 10:15, are represented in a copied but unapplied backup. The remaining five minutes are outside the latest completed backup in this example. These figures describe stage positions, not a guaranteed amount of lost data. Recovering additional log information may or may not be possible after a real failure, and bringing the service back also takes work beyond identifying the latest restored time.

Illustrative case study

In a fictional membership-service project, a team sees successful copy jobs and assumes its standby is current. A rehearsal reveals that restoration has fallen behind. The dashboard is revised to show backup coverage, copied coverage and applied coverage separately. The team also tests whether the application can connect and perform the required operations after an authorized recovery procedure. An AI support assistant that reads membership records is included in that exercise, since a healthy database alone does not prove that the complete service works. The case illustrates why staged progress and application recovery should both be checked; it does not report measured results from a real organization.

Common misconceptions and mistakes

The most common mistake is equating a transferred file with an updated database. Another is assuming that a standby implies automatic failover or zero data loss. A deliberate restore delay and an unexpected backlog can produce similar visible staleness while requiring different operational responses. Log shipping also should not be confused with collecting application logs for diagnostics. Transaction-log transfer serves a database recovery function. Finally, a secondary is not an independent guarantee that every unwanted change can be undone: copied changes and recovery procedures must be considered in the context of the incident and retained recovery material.

Business use

The concept helps translate continuity expectations into questions that can be tested. Specify how old an acceptable recovered state may be and how long the service may remain unavailable. Then verify whether the complete arrangement can meet those objectives under realistic conditions. Include the people responsible for identifying an incident, deciding on recovery and checking the application afterward. For an AI-backed service, consider supporting databases alongside model endpoints and other dependencies. A successful database-stage check should be reported for what it proves, while the broader service recovery result remains a separate measure.

Check your understanding

What was the toy secondary's applied coverage at 10:20?

Answer: Through 10:10.

How old was that state relative to the primary's stipulated coverage?

Answer: Ten minutes.

Why did a successful copy not prove currency?

Answer: The newest copied changes had not yet been restored.

Why was the fictional AI assistant included in the rehearsal?

Answer: The team needed evidence about the usable service as well as the database.

Related terms

Transaction log
database backup
warm standby
disaster recovery
recovery point objective
recovery time objective

Learn next

Draw a recovery pipeline with separate backup, copy and restore stages. Give each stage a different latest coverage time and calculate the applied-state gap. Then list the additional decisions and application checks needed before declaring service restored. Explain which observations establish data currency and which establish operational readiness.

Sources

Microsoft Learn, About Log Shipping (SQL Server): https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server?view=sql-server-ver17 The coverage-time calculation, duplicate-register analogy and membership-service case are original educational illustrations. The documentation supports the SQL Server mechanism and its stated capabilities; the entry does not promise automatic failover, zero loss or a particular recovery duration.

Previous: Log probabilities Next: Log-sum-exp trick