Log shipping

On this page 7

Log shipping is a database availability technique in which transaction log backups taken on a primary server are automatically copied to one or more secondary servers and restored there in sequence, keeping a warm standby copy of the database. In Backup & recovery, it sits between plain backup and full replication: it reuses the backup chain, yet provides a standby that can take over after a failure.

Log shipping is best known as a built-in feature of Microsoft SQL Server, but the same principle exists in other systems, for example WAL archiving with a standby in PostgreSQL and archived redo log shipping in Oracle Database. Its defining property is that changes travel as whole log files on a schedule, not as a continuous stream of individual records.

How log shipping works

Log shipping works as a three-step cycle of backup, copy and restore that repeats at fixed intervals. The process starts from a full database backup restored on the secondary in a state that still accepts further log restores, typically NORECOVERY or STANDBY in SQL Server.

  1. Backup job on the primary takes a transaction log backup, for example every 5 or 15 minutes.
  2. Copy job on the secondary transfers new log backup files from a shared folder to a local directory.
  3. Restore job on the secondary applies the copied files in log sequence order.
  4. An optional monitor server records the time of the last backup, copy and restore and raises alerts when thresholds are exceeded.

Because every log backup must be applied in order, the log chain must remain unbroken. A missing or corrupted file stops the restore job, and the secondary falls further behind until the gap is fixed, usually by restoring a fresh full or differential backup.

The jobs and their configurable settings determine how current the standby is, as shown below.

ComponentRuns onKey settingTypical value
Backup jobPrimary serverBackup frequency5 to 15 minutes
Copy jobSecondary serverCopy frequencyAligned with backup frequency
Restore jobSecondary serverRestore delay0 minutes to several hours
MonitorMonitor or primary serverAlert threshold2 to 4 times the backup interval
File cleanupPrimary and secondaryRetention of log files1 to 3 days
Log shipping jobs and their key settings

Recovery objectives and standby modes

The recovery point objective (RPO) of log shipping is bounded by the backup interval plus copy time, because changes made after the last shipped log backup exist only on the primary. With a 15-minute backup schedule, a sudden loss of the primary can mean up to about 15 minutes of lost transactions, unless the tail of the log can still be backed up from the damaged server.

The recovery time objective (RTO) depends on how many unapplied log files remain and on manual failover steps. Log shipping has no automatic failover: an administrator must restore remaining logs, bring the secondary online with recovery and redirect applications to it.

The secondary can be kept in one of two restore states, which changes what it can be used for.

ModeReadableBehavior during restoreBest use
NORECOVERYNoRestores run without disconnecting anyonePure disaster recovery standby
STANDBYYes, read-onlyUsers must be disconnected for each restoreReporting on slightly delayed data
Secondary database restore modes

A deliberate restore delay is a valuable safety feature. Delaying restores on the secondary by, for example, four hours creates a window in which an accidental mass delete or a faulty deployment can be stopped before it reaches the standby.

Log shipping differs from replication and clustering mainly in granularity, latency and automation. It ships complete log backup files, whereas streaming replication and availability groups send log records continuously and can fail over automatically.

MethodData transferTypical data lossAutomatic failover
Log shippingScheduled log backup filesMinutesNo
Streaming replicationContinuous log recordsSeconds or zero if synchronousWith external tooling
Always On availability groupsContinuous log recordsSeconds or zero if synchronousYes, in synchronous mode
Backup and restore onlyPeriodic backupsUp to the last backupNo
Database snapshotNone, local copy on writeNot a remote copyNo
Log shipping and alternative protection methods

Log shipping also strengthens point-in-time recovery, because the same transaction log backups serve both the standby and the backup archive. It is not a replacement for a backup retention policy, however: the standby holds only the current state, not history.

Practical configuration and operation

Successful log shipping depends on correct recovery model, reliable storage and active monitoring. In SQL Server, the primary database must use the full or bulk-logged recovery model, since the simple model does not keep log backups.

  • Store log backups on a network share reachable by all secondaries, separate from the primary disks.
  • Avoid running a separate log backup job on the same database, because it splits the chain between two locations.
  • Copy logins, jobs and linked servers to the secondary in advance, since log shipping transfers only the database.
  • Test failover regularly and document the exact role-reversal steps.

The following table shows how common operational symptoms map to causes and fixes.

SymptomLikely causeFix
Restore job reports a log sequence gapLog backup taken outside log shippingRestore the missing file or reinitialize
Secondary falls hours behindSlow network or large index maintenance logsIncrease bandwidth or schedule maintenance
Primary log file keeps growingBackup job failingFix the backup job and check disk space
Reports fail during restoresSTANDBY mode disconnects usersRestore less often or use a readable replica
Common log shipping problems

Limitations and risks

Log shipping protects one database at a time, so applications spanning several databases need coordinated failover to avoid inconsistent states between databases. It also offers no protection against logical corruption already shipped and restored, unless a restore delay is configured.

Large maintenance operations such as index rebuilds generate very large log backups, which can saturate the network and temporarily increase standby lag. Despite these limits, log shipping remains popular because it is simple, inexpensive and robust, and it works across long distances where synchronous replication would add unacceptable latency.

In simple terms

Think of a shop that keeps its sales ledger at the main office. Every fifteen minutes, someone photocopies the newest ledger pages and sends them to a branch office, where a clerk adds them to a duplicate ledger in the right order.

That is log shipping: the database regularly packs its latest changes into files and sends them to a backup server that replays them. If the main office burns down, the branch already has nearly everything. Only the last few minutes of entries may be missing, and someone has to switch over to the copy by hand.

A practical example

A regional hospital runs its appointment database on SQL Server in its main data center. The team configures log shipping to a secondary server in a second building 20 kilometers away, with log backups every 10 minutes and an alert if no restore occurs for 30 minutes.

A second secondary in STANDBY mode uses a two-hour restore delay and serves read-only reporting for administrative staff. One evening, a power failure takes the primary offline. The database administrator backs up the tail of the log from the surviving disks, restores it on the first secondary and brings it online.

Appointment booking resumes within about 25 minutes, with no lost transactions because the tail-log backup succeeded. Later the roles are reversed so that the original server becomes the new standby.