Log shipping doesn't come up in conference talks. There's no listener, no automatic failover, no quorum vote, just a backup job, a copy job, and a restore job running on a schedule. That plainness is exactly why it's still the right choice for a meaningful slice of disaster recovery scenarios, and why it's worth knowing well instead of reflexively reaching for an Availability Group every time DR comes up.
What It Actually Is
Three SQL Server Agent jobs, running on a schedule you control:
- Backup on the primary: a transaction log backup, on an interval (every 5, 15, 30 minutes, whatever your recovery point objective demands).
- Copy to the secondary: moves the backup file across the network.
- Restore on the secondary: applies the log backup
WITH NORECOVERY(orWITH STANDBY, see below), keeping the secondary database perpetually catching up.
That's the entire mechanism. No always-on connection is required between the two servers, if the network drops for an hour, backups just queue up and the copy job catches up once connectivity returns.
Where It Beats an Availability Group
| Situation | Why log shipping wins here |
|---|---|
| Standard Edition, or mixed editions | Works on SQL Server Standard Edition; AGs (beyond Basic AG's single-database limitation) require Enterprise |
| High-latency or unreliable links | No requirement for a continuously healthy connection or Windows Failover Clustering between sites |
| Simple audit and operational story | Three Agent jobs anyone on the team can read and troubleshoot, versus AG's WSFC, quorum, and listener machinery |
| Multiple independent DR targets | The same log backups can be shipped to several secondaries without additional cluster complexity |
| Delayed secondary as a safety net | A restore job that intentionally lags by an hour protects against someone truncating data and that mistake replicating instantly, an AG's near-real-time sync doesn't give you that buffer |
See Failover Clustering vs. SQL AlwaysOn for how these compare against the two AlwaysOn options; log shipping is the third leg of that stool, simpler than both, at the cost of manual failover and a real (if small) window of data loss between log backups.
The Restore-With-Standby Trick
WITH NORECOVERY leaves the database in a state nobody can query. Restoring WITH STANDBY = 'path\to\undo.tuf' instead brings the database up read-only between restores, using an undo file to hold uncommitted transactions that get rolled back again before the next log restore applies. Reports and ad-hoc queries can run against it in between restore cycles, giving you a reporting-offload option even without an Availability Group.
RESTORE LOG [MyDatabase]
FROM DISK = N'\\secondary\logshipping\MyDatabase_20260722_1400.trn'
WITH STANDBY = N'D:\LogShipping\MyDatabase_undo.tuf';Each subsequent restore briefly undoes the standby state, applies the next log backup, and re-establishes standby mode, so the read-only window is real but not continuous, plan reporting queries around the restore schedule, not against it.
Failover Is Manual, and That's the Trade-Off
- No automatic failover. Bringing the secondary online means applying the final tail-log backup (if the primary is reachable) and running
RESTORE DATABASE ... WITH RECOVERY, a deliberate, scripted action, not something that happens on its own. - Client redirection is your responsibility. There's no listener; applications need a DNS alias, connection string change, or manual reconfiguration pointed at the new primary after a failover.
- Recovery point is bounded by backup frequency. A 15-minute log backup interval means up to 15 minutes of potential data loss in an unplanned failover, tighten the interval if the business can't tolerate that window, but recognize it's never zero the way a synchronous AG replica can be.
Common Gotchas
- Forgetting the tail-log backup during a planned failover. Skip it and you lose everything committed since the last scheduled log backup, entirely avoidable during a planned failover, just easy to forget under pressure during an unplanned one.
- Restore job silently falling behind. If the restore job fails for a few cycles (permissions, disk space, a locked database from an open reporting connection under STANDBY mode) log backups keep piling up on the primary while nobody notices until the secondary is hours behind. Alert on restore job failures and on growing gaps between backup and restore timestamps.
- Log backup chain breaks. Any full backup taken outside the log shipping configuration, or a switch to SIMPLE recovery model even briefly, breaks the log chain and forces a full re-initialization of the secondary.
- Assuming it replaces monitoring. Log shipping has no built-in alerting for a stalled chain by default beyond what you configure explicitly, unlike an AG's dashboard, you have to build the "is this actually working" visibility yourself.
The Bottom Line
Log shipping doesn't compete with Availability Groups on features, it competes on simplicity, edition compatibility, and tolerance for unreliable networks. When automatic failover and near-zero data loss genuinely matter, an AG is worth its complexity. When they don't, and plenty of DR requirements genuinely don't, three Agent jobs anyone can read are often the more maintainable answer.