About Log Shipping (SQL Server)
SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.
In this Topic:
Provides a disaster-recovery solution for a single primary database and one or more secondary databases, each on a separate instance of SQL Server.
Supports limited read-only access to secondary databases (during the interval between restore jobs).
Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore (apply) the log backup. A longer delay can be useful, for example, if data is accidentally changed on the primary database. If the accidental change is noticed quickly, a delay can let you retrieve still unchanged data from a secondary database before the change is reflected there.
Log shipping consists of three operations:
Back up the transaction log at the primary server instance.
Copy the transaction log file to the secondary server instance.
Restore the log backup on the secondary server instance.
The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.
A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.
You can use a secondary database for reporting purposes.
In addition, you can configure alerts for your log shipping configuration.
A Typical Log Shipping Configuration
The following figure shows a log shipping configuration with the primary server instance, three secondary server instances, and a monitor server instance. The figure illustrates the steps performed by backup, copy, and restorejobs, as follows:
The primary server instance runs the backup job to back up the transaction log on the primary database. This server instance then places the log backup into a primary log-backup file, which it sends to the backup folder. In this figure, the backup folder is on a shared directory—the backup share.
Each of the three secondary server instances runs its own copy job to copy the primary log-backup file to its own local destination folder.
Each secondary server instance runs its own restore job to restore the log backup from the local destination folder onto the local secondary database.
The primary and secondary server instances send their own history and status to the monitor server instance.
Log shipping can be used with the following features or components of SQL Server:
AlwaysOn Availability Groups and database mirroring are mutually exclusive. A database that is configured for one of these features cannot be configured for the other.