SQL Server Log Shipping

Introduction

When a database goes down, the real question isn’t if you can recover — it’s how fast and how much data you can afford to lose. That’s where Disaster Recovery (DR) stops being theory and starts being an SLA discussion.

SQL Server offers multiple HA/DR options, but Log Shipping remains one of the most practical and widely used DR solutions — simple, predictable, and available even on Standard Edition. Despite being considered “old-school,” it is still highly relevant in real-world environments where cost, control, and reliability matter.

In this article, we’ll break down Log Shipping from a DBA’s perspective: what it is, when it makes sense, when it doesn’t, and how it compares to other HA/DR technologies like Database Mirroring and Availability Groups. We’ll also walk through a step-by-step configuration, discuss RPO/RTO implications, and clarify common misconceptions along the way.

If you want to truly understand Log Shipping as a DR solution — not just click through a wizard — this guide is for you.

Log shipping overview

SQL Server Log Shipping allows you to automatically ship transaction log backups from a primary database on a primary SQL Server instance to one or more secondary databases hosted on separate instances. Each transaction log backup is applied individually to the secondary databases.

An optional third instance, known as the monitor server, tracks the history and status of backup, copy, and restore operations and can raise alerts if these operations fail to run as scheduled.

Log shipping consists of 3 operations:

1. Back up the transaction log at the primary server instance.
2. Copy the transaction log file to the secondary server instance.
3. Restore the log backup on the secondary server instance.

These operations are executed through SQL Server Agent jobs. In the example shown below, GLADIATOR acts as the primary instance, while ROCKY serves as the secondary instance.

What is an SLA?

A service level agreement (SLA) is a contract between a service provider and a customer that defines the service to be provided and the level of performance to be expected. An SLA also describes how performance will be measured and approved, and what happens if performance levels are not met. — IBM

Why SLA matters?

Before choosing High Availability (HA) and Disaster Recovery (DR) solutions, it’s crucial to agree with stakeholders on the expected service levels. Understanding how much downtime and data loss is acceptable is essential before designing your environment.

• Disk corruption
• Disasters affecting data centers (floods, earthquakes, war, etc.)
• Accidental DROP, DELETE, or TRUNCATE operations

RPO and RTO: What They Are and Why They Matter

Recovery Point Objective (RPO)

• The maximum amount of data loss a business can tolerate in the event of a failure.
• Determines how frequently backups or transaction logs need to be captured.

Example: An RPO of 15 minutes means you cannot afford to lose more than 15 minutes of data.

Recovery Time Objective (RTO)

• The maximum time allowed to restore the system and resume normal operations after a failure.
• Determines how quickly failover or restoration must occur.

Example: An RTO of 2 hours means services must be back online within 2 hours of an outage.

Why it matters for Log Shipping

• Log Shipping can support specific RPO/RTO goals depending on backup frequency, copy schedule, and restore intervals.
• If your SLA requires near-zero downtime or near-real-time replication, Log Shipping alone may not meet the RTO or RPO — alternatives like Always On Availability Groups or Mirroring would be more appropriate.
• Conversely, if your RPO/RTO is moderate, Log Shipping provides a cost-effective, reliable DR solution.

Terms and definitions

• primary server: The instance of SQL Server that is your production server.
• primary database: The database on the primary server that you want to back up to another server. All administration of the log shipping configuration through SQL Server Management Studio is performed from the primary database.
• secondary server: The instance of SQL Server where you want to keep a warm standby copy of your primary database.
• secondary database: The warm standby copy of the primary database. The secondary database might be in either the RECOVERING state or the STANDBY state, which leaves the database available for limited read-only access.
• monitor server: An optional instance of SQL Server that tracks all of the details of log shipping, including:

1. When the transaction log on the primary database was last backed up.
2. When the secondary servers last copied and restored the backup files.
3. Information about any backup failure alerts.

• backup job: performs the backup operation, logs history to the local server and the monitor server, and deletes old backup files and history information. 
• copy job: copies the backup files from the primary server to a configurable destination on the secondary server and logs history on the secondary server and the monitor server. 
• restore job: restores the copied backup files to the secondary databases. It logs history on the local server and the monitor server, and deletes old files and old history information. 
• alert job: raises alerts for primary and secondary databases when a backup or restore operation doesn't complete successfully within a specified threshold.

When to Use Log Shipping

• When some data loss is acceptable, depending on the transaction log backup frequency.
• When you need a cost-effective Disaster Recovery solution.
• When a standby secondary database is required for limited read-only operations.

When NOT to Use Log Shipping

• When Automatic Failover is required.
• When near real-time data synchronization is expected.
• When High Availability is a strict requirement.

Log Shipping VS other HA/DR solutions

Practice

1. On the primary server, navigate to Databases, right-click the database that will act as the primary, and select Properties.
2. Go to Log Shipping and check Enable this as a primary database in a log shipping configuration.

3. Open Backup Settings and specify a network share or local folder path for transaction log backups.

Ensure that SQL Server Agent services on both servers have read/write access to this location.

Even if the backup folder is on the same host, you must explicitly provide the path.

4. Adjust the Backup Schedule as required by your SLA.

5. Click OK, then from the main Log Shipping page select Add Secondary Server Instance, click Connect, and choose the secondary instance.

6. Navigate to the Copy Files section:

• Specify the destination folder where backups will be copied.
• Ensure the SQL Server Agent on the secondary server has read/write access.
• Leave the Copy Job configuration at its default settings.

7. Navigate to the Restore Transaction Log section:

• No Recovery mode keeps the database inaccessible.
• Standby mode allows limited read-only access.
• Adjust the alert threshold based on your SLA.
• Review and modify the Restore Job Schedule if needed.

8. Click OK to complete the configuration.

Optional: You may configure a monitor server to track backup, copy, and restore activity, as well as failure alerts. By default, a monitor instance is not required.