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.
- Backup job on the primary takes a transaction log backup, for example every 5 or 15 minutes.
- Copy job on the secondary transfers new log backup files from a shared folder to a local directory.
- Restore job on the secondary applies the copied files in log sequence order.
- 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.
| Component | Runs on | Key setting | Typical value |
|---|---|---|---|
| Backup job | Primary server | Backup frequency | 5 to 15 minutes |
| Copy job | Secondary server | Copy frequency | Aligned with backup frequency |
| Restore job | Secondary server | Restore delay | 0 minutes to several hours |
| Monitor | Monitor or primary server | Alert threshold | 2 to 4 times the backup interval |
| File cleanup | Primary and secondary | Retention of log files | 1 to 3 days |
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.
| Mode | Readable | Behavior during restore | Best use |
|---|---|---|---|
| NORECOVERY | No | Restores run without disconnecting anyone | Pure disaster recovery standby |
| STANDBY | Yes, read-only | Users must be disconnected for each restore | Reporting on slightly delayed data |
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 compared with related techniques
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.
| Method | Data transfer | Typical data loss | Automatic failover |
|---|---|---|---|
| Log shipping | Scheduled log backup files | Minutes | No |
| Streaming replication | Continuous log records | Seconds or zero if synchronous | With external tooling |
| Always On availability groups | Continuous log records | Seconds or zero if synchronous | Yes, in synchronous mode |
| Backup and restore only | Periodic backups | Up to the last backup | No |
| Database snapshot | None, local copy on write | Not a remote copy | No |
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.
| Symptom | Likely cause | Fix |
|---|---|---|
| Restore job reports a log sequence gap | Log backup taken outside log shipping | Restore the missing file or reinitialize |
| Secondary falls hours behind | Slow network or large index maintenance logs | Increase bandwidth or schedule maintenance |
| Primary log file keeps growing | Backup job failing | Fix the backup job and check disk space |
| Reports fail during restores | STANDBY mode disconnects users | Restore less often or use a readable replica |
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.