Log Shipping: Measure Recoverability, Not Just Job Success
Diagnose backup, copy, and restore lag separately, plan intentional delays, and rehearse a log-shipping failover with a defensible data-loss estimate.
Log shipping can provide a useful recovery copy with relatively simple moving parts. Its main operational trap is treating a successful scheduled job as proof that the secondary can satisfy a recovery objective. A copy job can finish successfully while the primary has stopped producing backups. A restore job can succeed while the most recent file still sits on another server. The question is how far the recoverable database trails the business, not whether one step ran.
Follow the file through three stages
Draw the path for one specific log backup: creation on the primary, arrival at the secondary, and application to its database. Record the backup identity and timestamps at each stage. If backups are fresh but copied files are old, investigate share access, storage, and the copy process. If files arrive but restores fall behind, investigate restore errors, missing predecessors, reader conflicts, or insufficient restore throughput.
On an instance configured as the log-shipping monitor, the following read-only procedure provides the monitoring summary. Run it with the required administrative permissions. Review the primary and secondary results together, and check that the monitor itself is receiving recent information.
USE master;
EXEC sys.sp_help_log_shipping_monitor;
An old monitor record can mean telemetry failure rather than old data, so confirm the local job history and actual files before intervening. Conversely, dismissing every alert as a monitoring fault is dangerous. Build the alert around the maximum acceptable age and test notification delivery, not just the existence of an alert definition.
Consider backups every five minutes, copies every two, and restores every two. A transaction committed immediately after a backup waits for the next cycle and potentially both downstream cycles. These schedules are not a promise of five-minute data loss. Actual timing, failures, queues, and what remains accessible after the incident determine the outcome.
Make delay and retention explicit
A deliberate restore delay can leave a secondary before an accidental deletion long enough to intervene. That protection depends on noticing the event before the delayed restore applies it. It is not a replacement for independently retained backups: a late discovery can outlive the delay, and storage or credential compromise can affect several copies together.
Separate the age of the last copied backup from the age of the last restored backup. With intentional delay, a restore-age alert should reflect the configured delay plus an operational tolerance. The copy stage should still keep moving. Otherwise a dashboard may hide a stalled pipeline behind a blanket exemption for delay.
Retention must cover the delay, plausible outages, and the time needed to diagnose and catch up. If cleanup deletes an unapplied required log, a healthy later file cannot bridge the gap. Coordinate other backup products as well. An ordinary log backup taken elsewhere can introduce a required file that the shipping workflow never copies. Assign clear ownership of the complete sequence.
Rehearse the role change
Failover is a controlled manual process, not an automatic property of log shipping. First prevent the old primary from accepting conflicting writes. If it is accessible, determine whether a final log backup can capture remaining work, then copy and apply all required available logs. If it is unavailable, document the most recent recoverable position and the uncertainty about transactions after it.
Do not issue final recovery merely to see whether the database opens. Once recovered, the secondary cannot simply continue its existing restore sequence; reestablishing protection requires a planned process. Decide when to stop waiting for missing files, who accepts the possible loss, and how clients are redirected.
A STANDBY secondary may support read-only work between restores, but readers can interfere with the restore schedule unless disconnection is managed. Test the chosen mode and supported version combination. Finally verify logins, jobs, application connectivity, and external dependencies after the switch. Measure the complete exercise through the first correct business operation, then retain its file sequence and observed recovery time as operational evidence.
Technical references: Microsoft Learn: Log shipping overview · Microsoft Learn: Monitor summary · Microsoft Learn: Manual failover.