Configuring MS SQL Server Log Shipping for HA and DR

Configuring MS SQL Server Log Shipping for High Availability and Disaster Recovery

Imagine your primary database fails during peak hours, and the last backup is from yesterday—12 hours of data loss. An alternative is Log Shipping: asynchronous transaction log replication to a secondary server, with refreshed backups every 15–30 minutes. RPO: 15 minutes, RTO: 5–10 minutes on failover. No Enterprise licenses or expensive hardware needed.

Our engineers with over 10 years of experience implement DR solutions for SQL Server where budgets for Always On AG or FCI are unavailable. Log Shipping is a proven mechanism, working from SQL Server 2000 to the latest versions. The principle is simple: the primary server backs up the transaction log, copies the file to the secondary, and the secondary restores it. All handled by SQL Server Agent jobs, requiring no additional infrastructure.

Why Log Shipping is a Reliable DR Solution?

Log Shipping offers cost savings compared to Always On AG: implementation costs 3–5 times lower, no Enterprise licenses, and no identical hardware required. An RPO of 15–30 minutes meets most business requirements. Asynchrony is a trade-off: during a failure, the last unarchived interval is lost. But this is the price for cost-effectiveness. For critical systems with zero data loss, Always On AG is the choice; however, for typical DR, Log Shipping is optimal.

How It Works and Where It Typically Breaks

Three jobs: backup (on primary), copy (on secondary), restore (on secondary). Optionally, a monitor server that tracks lag and alerts on issues.

The most common mistake during self-setup: the backup job runs, but the copy job stays silent, showing in Error Log on secondary: Cannot open backup device '\\PRIMARY\LogShipping\AppDB_20240315_0230.bak'. Operating system error 5. Diagnosis: Access Denied on the share. The SQL Server Agent on secondary runs under an account without permissions to the primary's UNC path. We solve this by configuring share permissions or changing the service account. Such cases are our daily experience.

Microsoft documentation recommends using a separate account for SQL Server Agent with minimal permissions on the UNC path.

Example monitoring script:

-- Check log shipping status after setup
SELECT primary_server, primary_database, last_backup_file, last_backup_date, DATEDIFF(MINUTE, last_backup_date, GETDATE()) AS minutes_since_backup
FROM msdb.dbo.log_shipping_monitor_primary;

SELECT secondary_server, secondary_database, last_restored_file, last_restored_date, restore_threshold, DATEDIFF(MINUTE, last_restored_date, GETDATE()) AS minutes_since_restore
FROM msdb.dbo.log_shipping_monitor_secondary;

How to Configure Log Shipping with Minimal RPO?

Configuration via T-SQL is a reproducible approach. The GUI in SSMS is fine for learning, but for automation, scripts are best.

On the primary server, initialize the backup job:

-- On primary: configure backup job
EXEC msdb.dbo.sp_add_log_shipping_primary_database
    @database = N'AppDB',
    @backup_directory = N'\\SQLPRIMARY\LogShipping',
    @backup_share = N'\\SQLPRIMARY\LogShipping',
    @backup_job_name = N'LSBackup_AppDB',
    @backup_retention_period = 4320,
    @backup_threshold = 60,
    @threshold_alert_enabled = 1,
    @history_retention_period = 5760,
    @backup_compression = 1,
    @primary_id = @LS_BackupJobId OUTPUT;

On the secondary, configure copy and restore jobs:

-- On secondary: configure copy + restore
EXEC msdb.dbo.sp_add_log_shipping_secondary_primary
    @primary_server = N'SQLPRIMARY',
    @primary_database = N'AppDB',
    @backup_source_directory = N'\\SQLPRIMARY\LogShipping',
    @backup_destination_directory = N'D:\LogShippingLocal',
    @copy_job_name = N'LSCopy_SQLPRIMARY_AppDB',
    @restore_job_name = N'LSRestore_SQLPRIMARY_AppDB',
    @file_retention_period = 4320,
    @secondary_id = @LS_Secondary OUTPUT;

EXEC msdb.dbo.sp_add_log_shipping_secondary_database
    @secondary_database = N'AppDB',
    @primary_server = N'SQLPRIMARY',
    @primary_database = N'AppDB',
    @restore_delay = 0,
    @restore_mode = 1,
    @disconnect_users = 0,
    @restore_threshold = 45,
    @threshold_alert_enabled = 1,
    @history_retention_period = 5760,
    @secondary_id = @LS_Secondary;

@restore_mode = 1 (STANDBY) allows reading the secondary between restores—convenient for reporting. @restore_mode = 0 (NORECOVERY) gives minimal RTO on failover. @restore_delay is the delay in applying logs. Protection against accidental DROP TABLE: set a 60-minute delay—you have one hour to stop the restore and extract data.

Initializing the Secondary Database

Before starting log shipping, restore a full backup of the primary onto the secondary in NORECOVERY mode:

-- On primary: full backup
BACKUP DATABASE AppDB TO DISK = N'\\SQLPRIMARY\LogShipping\AppDB_init.bak' WITH COMPRESSION, STATS = 10;

-- On secondary: restore
RESTORE DATABASE AppDB FROM DISK = N'\\SQLPRIMARY\LogShipping\AppDB_init.bak' WITH NORECOVERY, MOVE 'AppDB' TO 'D:\SQLData\AppDB.mdf', MOVE 'AppDB_log' TO 'D:\SQLLog\AppDB_log.ldf', STATS = 10;

After that, start the jobs—they will begin copying and applying transaction log files. Typical setup timelines: one database – 1 day, multiple databases with monitor server – 2-3 days, migration – up to 3 days.

Switchover During Failover

-- On secondary during failover:
-- 1. Manually apply all available logs
RESTORE LOG AppDB FROM DISK = N'D:\LogShippingLocal\AppDB_last.trn' WITH NORECOVERY;
-- 2. Bring database online
RESTORE DATABASE AppDB WITH RECOVERY;
-- 3. Switch application connection string to secondary server

Comparison: Log Shipping vs Always On AG

Typical RPO/RTO Depending on Configuration

-- Detailed lag monitoring
SELECT db.name, ls.primary_database, ls.last_backup_date, ls.last_restored_date,
       DATEDIFF(MINUTE, ls.last_backup_date, GETDATE()) AS backup_age_min,
       DATEDIFF(MINUTE, ls.last_restored_date, GETDATE()) AS restore_age_min
FROM sys.databases db
JOIN msdb.dbo.log_shipping_monitor_primary ls ON db.name = ls.primary_database;

What's Included

• Audit of current SQL Server infrastructure
• Configuration of backup/copy/restore jobs
• Monitor server setup with alerts
• Failover and switchback testing
• Documentation and recovery Runbook
• On-call team training

Our engineers hold Microsoft certifications and have completed over 50 successful Log Shipping projects. Get a consultation—we will assess your task and propose the optimal replication scheme.

Common Setup Mistakes

• Incorrect UNC path permissions (Access Denied)
• Missing NORECOVERY when initializing secondary
• Wrong restore_delay—data may be lost during quick failover
• Ignoring lag monitoring—problems only become visible during a disaster

To avoid these issues, contact us—we will set up Log Shipping turnkey in 1–3 days, depending on complexity.