A SQL Server disaster recovery technique that automatically backs up transaction logs on a primary database and restores them on one or more secondary databases on a scheduled interval.
- Log shipping provides a secondary database that is typically minutes to hours behind the primary: It serves as a warm standby for disaster recovery.
- Log shipping consists of three components: A Backup job on the primary server, a Copy job on the secondary server, and a Restore job on the secondary server, each running on scheduled intervals managed by SQL Server Agent.
- Secondary databases in log shipping are kept in a restoring state: Read-only access is possible when using
STANDBYmode, but secondary databases cannot accept writes until failover occurs. - Log shipping is simpler to configure than Always On Availability Groups but provides fewer features: It does not support automatic failover or readable secondaries without manual intervention.
- A common use case for log shipping is disaster recovery for SQL Server Standard Edition environments: It can also serve as a secondary disaster recovery tier alongside primary high-availability solutions.
- DBAs should alert on log shipping failures immediately: A broken log shipping chain can quickly increase potential data loss and jeopardize disaster recovery objectives.
- Related terms: Always On Availability Groups, Backup, Transaction Log, Full Recovery Model, RPO.
