Mastering SQL Server Log Shipping: Setup, Jobs, and Troubleshooting

SQL Server log shipping is a long‑standing disaster‑recovery technology that creates redundant copies of a database by continuously backing up transaction logs on a primary server and restoring them on one or more secondary servers.

Architecture and Server Roles

The primary server hosts the production database, which must be in full or bulk‑logged recovery mode; simple recovery is unsupported because it prevents log backups.

The secondary servers hold the copied databases. Each secondary can be configured in one of two states:

NORECOVERY : the database is not readable and cannot be rolled forward.

STANDBY : the database is readable; uncommitted transactions are stored in a Transaction Undo File (TUF) and applied after each restore, allowing reporting workloads.

A single primary can feed multiple secondaries, and a secondary can host copies from several primaries, reducing hardware costs.

An optional monitor server tracks the last backup time on the primary, the last copy and restore times on each secondary, and any failure alerts. It should be independent of the primary and secondaries to avoid a single point of failure.

Log Shipping Jobs

Four SQL Server Agent jobs drive the process, each typically running every 15 minutes (adjustable):

Backup job on the primary creates a transaction‑log backup and places the file in a shared folder.

Copy job on each secondary copies the backup file from the shared folder to a local directory.

Restore job on each secondary restores the copied log file, leaving the database in NORECOVERY or STANDBY mode as configured.

Alert job (on the monitor or on each server if no monitor is used) raises an error when a job fails to complete within its expected window.

From SQL Server 2005 onward the jobs invoke the executable sqllogship.exe. Example commands:

sqllogship.exe -Backup <GUID> -server <instance_name> sqllogship.exe -Copy <GUID> -server <instance_name> sqllogship.exe -Restore <GUID> -server <instance_name>

The <GUID> identifies the specific database, and <instance_name> identifies the SQL Server instance.

Execution Intervals and Data‑Loss Window

The overall latency equals the sum of the backup, copy, and restore intervals. With the default 15‑minute schedule, the worst‑case data loss is roughly 15 minutes, because the last successful backup defines the loss window. Reducing the backup interval shortens potential loss but increases load on the primary.

SQL Server Agent enforces a minimum interval of 10 seconds, so zero‑loss is unattainable with log shipping alone.

Failover Procedure

When the primary fails, a manual failover is required:

Back up the tail of the primary log (if the primary is still reachable) using WITH NORECOVERY to prevent further changes.

Copy any remaining backup files to each secondary.

Restore all pending log backups on the chosen secondary in order, then run RESTORE DATABASE … WITH RECOVERY to bring it online.

Manually migrate logins from the old primary to the new primary because log shipping does not copy the master database.

Update application connection strings or aliases to point to the new primary.

After the new primary is online, re‑configure log shipping to reverse the roles if desired.

Monitoring and Troubleshooting

SQL Server Management Studio reports (Standard Reports → Transaction Log Shipping Status) show job status, last successful backup, copy, and restore files. The stored procedure sp_help_log_shipping_monitor queries the tables msdb.dbo.log_shipping_monitor_primary and msdb.dbo.log_shipping_monitor_secondary for the same data.

When a job fails, examine its history via SSMS or query msdb..sysjobs and msdb..sysjobhistory. Common failure causes include:

Insufficient permissions for the Agent account to access the shared folder.

Missing or inaccessible shared directory.

Log‑backup file name changes that break timestamp‑based selection.

File locks by antivirus or monitoring tools preventing restore.

Corrupted backup files or LSN mismatches (start LSN > secondary’s last LSN).

LSN information can be retrieved with queries against msdb..backupset and RESTORE HEADERONLY, and the secondary’s current LSN can be checked with DBCC DBTABLE('dbname').

Key Takeaways

Log shipping provides a reliable, low‑cost DR solution but requires careful job scheduling, monitoring, and manual failover steps. It is unsuitable for scenarios demanding zero data loss or automatic role switching; for those, technologies such as Always On Availability Groups are more appropriate.