How to Set Up and Monitor SQL Server Log Shipping

SQL Server log shipping is a proven disaster recovery solution that maintains one or more secondary copies of a production database by continuously backing up, copying and restoring transaction-log backups.

It is available in SQL Server 2019 and other supported SQL Server versions. It is commonly used when organisations need a cost-effective warm standby database without the clustering requirements and licensing complexity associated with some high-availability technologies.

However, log shipping is not an automatic failover solution. A secondary database must be brought online manually, and applications must then be redirected to the new server.

This guide explains how to configure SQL Server log shipping using Transact-SQL, monitor its health, troubleshoot common problems and perform a controlled or emergency failover.

How SQL Server log shipping works

A standard log-shipping configuration contains three operations:

1. A backup job creates transaction-log backups on the primary server.

2. A copy job transfers those backup files to the secondary server.

3. A restore job applies the copied log backups to the secondary database.

An optional monitor server can collect status information and raise alerts when backups or restores fall outside their configured thresholds.

The basic flow is:

Primary database
      ↓
Transaction-log backup
      ↓
Shared backup folder
      ↓
Copy job on secondary
      ↓
Secondary destination folder
      ↓
Restore job
      ↓
Secondary database

Microsoft provides additional architectural information in its SQL Server log shipping overview.

Log shipping benefits and limitations

Benefits

SQL Server log shipping provides:

• A relatively simple disaster recovery architecture

• Support for one or more secondary servers

• Flexible backup, copy and restore schedules

• A configurable recovery point objective

• Optional delayed restoration for protection against accidental changes

• Limited read-only reporting when the secondary uses standby mode

• Support for geographically separated SQL Server instances

• Lower infrastructure complexity than cluster-based solutions

Limitations

Log shipping also has several important limitations:

• Failover is manual.

• There is no built-in listener or virtual server name.

• SQL Server logins, Agent jobs and other server objects are not automatically synchronised.

• The secondary database is not continuously synchronised; it is only current up to its last restored log backup.

• A missing transaction-log backup breaks the restore sequence.

• Application redirection must be planned separately.

• Return to the original primary requires reverse log shipping or reinitialisation.

Log shipping should therefore be considered a disaster recovery solution rather than a complete high-availability solution.

Example configuration

The following examples use these values:

Replace these values with those used in your environment.

Step 1: Confirm the prerequisites

SQL Server Agent must be installed, running and configured to start automatically on both servers. SQL Server Express is unsuitable because it does not include SQL Server Agent.

Check the SQL Server versions:

SELECT
    @@SERVERNAME AS InstanceName,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition;

Check the SQL Server Agent service:

SELECT
    servicename,
    startup_type_desc,
    status_desc,
    service_account
FROM sys.dm_server_services
WHERE servicename LIKE N'SQL Server Agent%';

The primary database must use either the FULL or BULK_LOGGED recovery model.

SELECT
    name,
    state_desc,
    recovery_model_desc,
    log_reuse_wait_desc
FROM sys.databases
WHERE name = N'AppDB';

If the database currently uses the SIMPLE recovery model, change it:

ALTER DATABASE [AppDB] SET RECOVERY FULL;
GO

A full database backup must be taken after changing from SIMPLE to FULL to establish the transaction-log backup chain.

Step 2: Prepare folders and permissions

Create the following folders:

PRIMARY01:
D:\LogShipping\AppDB

SECONDARY01:
D:\LogShippingCopy\AppDB

Share the primary folder as:

\\PRIMARY01\LSBackup\AppDB

Configure the following permissions:

• The primary SQL Server service account requires Modify permission on the primary backup folder.

• The primary SQL Server Agent account requires access to the primary backup folder.

• The secondary SQL Server Agent account requires Read permission on the network share.

• The secondary SQL Server Agent account requires Modify permission on the secondary destination folder.

• The secondary SQL Server Database Engine account requires Read permission on the secondary destination folder.

Use a UNC network path rather than a mapped drive. Windows services usually cannot access user-specific mapped drives reliably.

For a cross-domain or workgroup configuration, service-account authentication and SMB permissions require additional planning.

Step 3: Review existing transaction-log backups

Before enabling log shipping, identify every process that performs transaction-log backups for the database.

This is especially important when the server uses VDI-based backup software such as enterprise backup agents, VM-aware backup products or cloud backup extensions.

Check recent backups:

SELECT TOP (50)
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
    END AS BackupType,
    bs.is_copy_only,
    bs.user_name,
    bmf.physical_device_name
FROM msdb.dbo.backupset AS bs
LEFT JOIN msdb.dbo.backupmediafamily AS bmf
    ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name = N'AppDB'
ORDER BY bs.backup_start_date DESC;

Other normal transaction-log backup jobs must be disabled once log shipping becomes responsible for the log-backup chain.

If another system takes a normal log backup between two log-shipping backups, the secondary will not receive that file. The next restore can then fail because of a missing LSN range.

Full backups, differential backups and carefully controlled COPY_ONLY operations can continue, but the overall backup strategy should be tested.

Step 4: Initialise the secondary database

Take a full backup on the primary:

BACKUP DATABASE [AppDB]
TO DISK = N'D:\LogShipping\AppDB\AppDB_Initial.bak'
WITH
    COPY_ONLY,
    COMPRESSION,
    CHECKSUM,
    INIT,
    STATS = 10;
GO

Verify the backup:

RESTORE VERIFYONLY
FROM DISK = N'D:\LogShipping\AppDB\AppDB_Initial.bak'
WITH CHECKSUM;
GO

Copy the full backup to:

D:\LogShippingCopy\AppDB\AppDB_Initial.bak

On the secondary, identify the database’s logical file names:

RESTORE FILELISTONLY
FROM DISK = N'D:\LogShippingCopy\AppDB\AppDB_Initial.bak';
GO

Restore the database using NORECOVERY:

RESTORE DATABASE [AppDB]
FROM DISK = N'D:\LogShippingCopy\AppDB\AppDB_Initial.bak'
WITH
    MOVE N'AppDB_Data'
        TO N'E:\SQLData\AppDB.mdf',
    MOVE N'AppDB_Log'
        TO N'F:\SQLLog\AppDB_log.ldf',
    NORECOVERY,
    CHECKSUM,
    STATS = 10;
GO

If the database has multiple data or log files, include a MOVE clause for every file returned by RESTORE FILELISTONLY.

Confirm the secondary state:

SELECT
    name,
    state_desc
FROM sys.databases
WHERE name = N'AppDB';

The expected state is RESTORING. Do not recover the secondary database during configuration.

If Transparent Data Encryption is enabled, restore the required TDE certificate and private key into master on the secondary before attempting to restore the database.

Step 5: Configure the primary database

Run the following on the primary server:

USE master;
GO

DECLARE @BackupJobId uniqueidentifier;
DECLARE @PrimaryId uniqueidentifier;

EXEC master.dbo.sp_add_log_shipping_primary_database
    @database                 = N'AppDB',
    @backup_directory         = N'D:\LogShipping\AppDB',
    @backup_share             = N'\\PRIMARY01\LSBackup\AppDB',
    @backup_job_name          = N'LSBackup_AppDB',
    @backup_retention_period  = 4320,
    @backup_threshold         = 20,
    @threshold_alert          = 14420,
    @threshold_alert_enabled  = 1,
    @history_retention_period = 5760,
    @backup_compression       = 1,
    @backup_job_id            = @BackupJobId OUTPUT,
    @primary_id               = @PrimaryId OUTPUT;

EXEC msdb.dbo.sp_add_jobschedule
    @job_id               = @BackupJobId,
    @name                 = N'LS Backup every 5 minutes',
    @enabled              = 1,
    @freq_type            = 4,
    @freq_interval        = 1,
    @freq_subday_type     = 4,
    @freq_subday_interval = 5,
    @active_start_time    = 000000;

EXEC msdb.dbo.sp_update_job
    @job_id  = @BackupJobId,
    @enabled = 1;

EXEC master.dbo.sp_add_log_shipping_alert_job;

SELECT
    @BackupJobId AS BackupJobId,
    @PrimaryId AS PrimaryId;
GO

This configuration:

• Creates a log backup every five minutes.

• Retains backup files for three days.

• Generates error 14420 if no log backup occurs within 20 minutes.

• Retains log-shipping history for four days.

• Enables backup compression.

Step 6: Configure the secondary database

Run the following on the secondary server:

USE master;
GO

DECLARE @CopyJobId uniqueidentifier;
DECLARE @RestoreJobId uniqueidentifier;
DECLARE @SecondaryId uniqueidentifier;

EXEC master.dbo.sp_add_log_shipping_secondary_primary
    @primary_server               = N'PRIMARY01',
    @primary_database             = N'AppDB',
    @backup_source_directory      = N'\\PRIMARY01\LSBackup\AppDB',
    @backup_destination_directory = N'D:\LogShippingCopy\AppDB',
    @copy_job_name                = N'LSCopy_PRIMARY01_AppDB',
    @restore_job_name             = N'LSRestore_PRIMARY01_AppDB',
    @file_retention_period        = 4320,
    @copy_job_id                  = @CopyJobId OUTPUT,
    @restore_job_id               = @RestoreJobId OUTPUT,
    @secondary_id                 = @SecondaryId OUTPUT;

EXEC msdb.dbo.sp_add_jobschedule
    @job_id               = @CopyJobId,
    @name                 = N'LS Copy every 2 minutes',
    @enabled              = 1,
    @freq_type            = 4,
    @freq_interval        = 1,
    @freq_subday_type     = 4,
    @freq_subday_interval = 2,
    @active_start_time    = 000000;

EXEC msdb.dbo.sp_add_jobschedule
    @job_id               = @RestoreJobId,
    @name                 = N'LS Restore every 5 minutes',
    @enabled              = 1,
    @freq_type            = 4,
    @freq_interval        = 1,
    @freq_subday_type     = 4,
    @freq_subday_interval = 5,
    @active_start_time    = 000200;

EXEC master.dbo.sp_add_log_shipping_secondary_database
    @secondary_database       = N'AppDB',
    @primary_server           = N'PRIMARY01',
    @primary_database         = N'AppDB',
    @restore_delay            = 0,
    @restore_all              = 1,
    @restore_mode             = 0,
    @disconnect_users         = 1,
    @restore_threshold        = 20,
    @threshold_alert          = 14421,
    @threshold_alert_enabled  = 1,
    @history_retention_period = 5760;

EXEC msdb.dbo.sp_update_job
    @job_id  = @CopyJobId,
    @enabled = 1;

EXEC msdb.dbo.sp_update_job
    @job_id  = @RestoreJobId,
    @enabled = 1;

EXEC master.dbo.sp_add_log_shipping_alert_job;

SELECT
    @CopyJobId AS CopyJobId,
    @RestoreJobId AS RestoreJobId,
    @SecondaryId AS SecondaryId;
GO

The important restore options are:

NORECOVERY is generally preferable for a dedicated DR server. STANDBY permits read-only access between restores but can cause restore failures or user disconnections when reporting sessions remain connected.

Step 7: Register the secondary on the primary

Run this command on the primary:

USE master;
GO

EXEC master.dbo.sp_add_log_shipping_primary_secondary
    @primary_database   = N'AppDB',
    @secondary_server   = N'SECONDARY01',
    @secondary_database = N'AppDB';
GO

Microsoft documents the complete stored-procedure sequence in its SQL Server log shipping configuration guide.

Step 8: Test the backup, copy and restore jobs

Start the backup job on the primary:

EXEC msdb.dbo.sp_start_job
    @job_name = N'LSBackup_AppDB';

Check its status:

EXEC msdb.dbo.sp_help_job
    @job_name = N'LSBackup_AppDB';

After it succeeds, start the copy job on the secondary:

EXEC msdb.dbo.sp_start_job
    @job_name = N'LSCopy_PRIMARY01_AppDB';

After the copy job succeeds, start the restore job:

EXEC msdb.dbo.sp_start_job
    @job_name = N'LSRestore_PRIMARY01_AppDB';

Confirm that:

• A .trn file was generated in the primary backup folder.

• The file was copied to the secondary destination folder.

• The restore job processed the file successfully.

• The secondary database remains in RESTORING.

• The three jobs continue running according to schedule.

SQL Server Agent jobs are asynchronous. Wait for each job to complete before starting the next job during the initial test.

Monitoring SQL Server log shipping

A log-shipping implementation is only effective when backup, copy and restore activity is actively monitored.

The most important measurements are:

• Time since the last log backup

• Time since the last file copy

• Time since the last log restore

• Restore latency

• SQL Server Agent job failures

• Missing or out-of-sequence files

• Available disk space

• Access to the backup network share

Monitor the primary backup status

Run on the primary:

SELECT
    primary_server,
    primary_database,
    last_backup_file,
    last_backup_date,
    DATEDIFF(MINUTE, last_backup_date, GETDATE())
        AS BackupAgeMinutes,
    backup_threshold,
    CASE
        WHEN last_backup_date IS NULL
            THEN 'NOT STARTED'
        WHEN DATEDIFF(MINUTE, last_backup_date, GETDATE())
             > backup_threshold
            THEN 'ALERT'
        ELSE 'HEALTHY'
    END AS BackupStatus
FROM msdb.dbo.log_shipping_monitor_primary;

A healthy backup job should create transaction-log backups within the configured threshold.

Monitor secondary copy and restore status

Run on the secondary:

SELECT
    ls.primary_server,
    ls.primary_database,
    ls.secondary_server,
    ls.secondary_database,
    ls.last_copied_file,
    ls.last_copied_date,
    ls.last_restored_file,
    ls.last_restored_date,
    ls.last_restored_latency AS RestoreLatencyMinutes,
    DATEDIFF(MINUTE, ls.last_restored_date, GETDATE())
        AS MinutesSinceLastRestore,
    ls.restore_threshold,
    d.state_desc AS DatabaseState,
    CASE
        WHEN ls.last_restored_date IS NULL
            THEN 'NOT STARTED'
        WHEN DATEDIFF(MINUTE, ls.last_restored_date, GETDATE())
             > ls.restore_threshold
            THEN 'ALERT'
        ELSE 'HEALTHY'
    END AS RestoreStatus
FROM msdb.dbo.log_shipping_monitor_secondary AS ls
LEFT JOIN sys.databases AS d
    ON d.name = ls.secondary_database;

When NORECOVERY is used, RESTORING is the correct secondary database state and should not be treated as an error.

View the overall log-shipping status

Run on the primary, secondary or dedicated monitor server:

EXEC master.dbo.sp_help_log_shipping_monitor;

The result uses:

• 0 for healthy

• 1 for a threshold or agent problem

A dedicated monitor server provides the most complete centralised view. It must be included when log shipping is first configured. Adding or changing a monitor later normally requires the log-shipping configuration to be removed and recreated.

Microsoft describes the available status procedures and monitoring tables in its log shipping monitoring documentation.

Review log-shipping errors

Run this query on the affected server:

SELECT TOP (100)
    CASE agent_type
        WHEN 0 THEN 'Backup'
        WHEN 1 THEN 'Copy'
        WHEN 2 THEN 'Restore'
    END AS AgentType,
    database_name,
    log_time,
    source,
    message
FROM msdb.dbo.log_shipping_monitor_error_detail
ORDER BY
    log_time DESC,
    sequence_number;

This helps identify network failures, missing files, restore-sequence errors, permissions problems and unavailable destinations.

Check SQL Server Agent job results

SELECT
    j.name AS JobName,
    j.enabled,
    CASE s.last_run_outcome
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 3 THEN 'Cancelled'
        WHEN 5 THEN 'Unknown'
        ELSE 'Other'
    END AS LastRunOutcome,
    CASE
        WHEN s.last_run_date = 0 THEN NULL
        ELSE msdb.dbo.agent_datetime(
            s.last_run_date,
            s.last_run_time
        )
    END AS LastRunDateTime
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.syscategories AS c
    ON c.category_id = j.category_id
LEFT JOIN msdb.dbo.sysjobservers AS s
    ON s.job_id = j.job_id
WHERE c.name LIKE N'Log Shipping%'
ORDER BY j.name;

Log-shipping job success alone is not enough. A copy job may succeed without copying a new file, so the last copied and restored timestamps must also be monitored.

Configure log-shipping alerts

SQL Server uses two important log-shipping error numbers:

After configuring Database Mail and assigning a mail profile to SQL Server Agent, create SQL Server Agent alerts for these error numbers and notify the DBA operator.

The alert threshold must be longer than the normal backup and restore interval. For example, a five-minute backup schedule could reasonably use a 15- or 20-minute alert threshold.

Common SQL Server log-shipping problems

Backup job fails

Common causes include:

• Database changed to SIMPLE recovery

• Backup folder unavailable

• Insufficient disk space

• SQL Server service-account permissions

• Competing transaction-log backup software

• SQL Server Agent stopped

• Backup application or VDI errors

Copy job fails

Check:

• Network connectivity

• DNS resolution

• SMB share availability

• Share and NTFS permissions

• Secondary SQL Server Agent service account

• Firewall rules

• File-retention settings

Restore job reports an LSN gap

An LSN gap means a required transaction-log backup is missing.

This frequently occurs when another backup product performs an independent normal transaction-log backup. Identify the missing file and restore it before processing later files.

Do not use WITH RECOVERY to bypass the problem. Once the secondary database is recovered, additional transaction logs cannot be applied without reinitialising or establishing another valid restore sequence.

Restore job cannot obtain exclusive access

This is common when the secondary uses STANDBY and reporting users remain connected.

Options include:

• Enable disconnect users for the restore job.

• Terminate reporting sessions before each restore.

• Increase the restore interval.

• Change the secondary to NORECOVERY if reporting access is unnecessary.

Log-shipping latency keeps increasing

Increasing latency can indicate:

• Slow network transfer

• Large transaction-log backups

• Restore job duration exceeding its schedule

• Slow secondary storage

• Long-running or minimally logged transactions

• Insufficient CPU or I/O capacity

• Antivirus scanning backup files

• SQL Server Agent scheduling delays

Compare the backup, copy and restore timestamps to determine where the delay occurs.

Performing a planned log-shipping failover

A planned failover should be performed during an approved maintenance window.

The high-level sequence is:

1. Stop all application writes.

2. Disable the primary log-shipping backup job.

3. Copy and restore every outstanding log-shipping file.

4. Take a final tail-log backup using WITH NORECOVERY.

5. Restore the tail-log backup on the secondary.

6. Recover the secondary database.

7. Redirect applications to the new primary.

8. Validate logins, jobs, permissions and application functionality.

9. Configure log shipping in the reverse direction.

Take the final tail-log backup on the original primary:

BACKUP LOG [AppDB]
TO DISK =
    N'\\PRIMARY01\LSBackup\AppDB\AppDB_FailoverTail.trn'
WITH
    NORECOVERY,
    CHECKSUM,
    INIT,
    STATS = 10;
GO

Restore it on the secondary:

RESTORE LOG [AppDB]
FROM DISK =
    N'D:\LogShippingCopy\AppDB\AppDB_FailoverTail.trn'
WITH
    NORECOVERY,
    CHECKSUM,
    STATS = 10;
GO

If the tail restore succeeds, bring the secondary online:

RESTORE DATABASE [AppDB] WITH RECOVERY;
GO

Do not recover the secondary until every required transaction-log backup has been restored successfully.

Microsoft provides the supported sequence in its log shipping failover guide.

Emergency failover and potential data loss

If the primary server is unavailable, apply every log backup that has already reached the secondary and then recover the secondary database:

RESTORE DATABASE [AppDB] WITH RECOVERY;
GO

Any transaction that was committed after the last successfully restored log backup may be lost if a tail-log backup cannot be obtained.

A five-minute backup schedule does not guarantee a five-minute recovery point. Actual data loss depends on:

• Whether the backup job completed

• Whether the file reached the secondary

• Whether the restore job applied it

• Network and storage delays

• Any unresolved job failures

The old primary must be isolated before the secondary accepts production writes. Otherwise, both databases may become writable, creating a split-brain situation with conflicting data.

Prepare server-level objects before failover

Database users are included in the shipped database, but their corresponding server logins are stored in master and are not shipped.

Prepare the following on the secondary:

• SQL Server logins with matching SIDs

• SQL Server Agent jobs

• Credentials and proxies

• Linked servers

• Database Mail configuration

• Server-level permissions

• Encryption certificates

• Endpoints

• Application connection configuration

Synchronising login names without preserving their SIDs can create orphaned database users after failover.

Log-shipping best practices

For a production environment:

• Use dedicated domain service accounts.

• Place the backup share on resilient storage.

• Encrypt and protect access to backup files.

• Enable backup checksums.

• Use backup compression where appropriate.

• Keep the backup, copy and restore schedules closely aligned.

• Monitor backup age, restore age and restore latency.

• Configure email alerts for errors 14420 and 14421.

• Monitor disk capacity on both servers.

• Document application redirection procedures.

• Prevent competing transaction-log backups.

• Synchronise server-level objects.

• Perform regular restore and failover tests.

• Test the reverse log-shipping and failback process.

• Maintain an approved emergency runbook.

• Record the expected RPO and RTO.

Frequently asked questions

Does SQL Server log shipping provide automatic failover?

No. Log shipping requires manual database recovery and application redirection. If automatic failover is required, evaluate Always On availability groups or failover cluster instances.

Can users query the secondary database?

Yes, if the secondary uses STANDBY mode. Users must normally be disconnected when additional transaction-log backups are restored.

A secondary using NORECOVERY cannot be queried.

Can full backups continue after log shipping is enabled?

Yes. Full and differential backups do not normally break log shipping. However, ordinary transaction-log backups taken by another process can interrupt the sequence available to the log-shipping restore job.

What recovery point can log shipping provide?

The recovery point depends on the backup frequency and operational delay. A five-minute backup interval provides a nominal five-minute RPO, but the real recovery point is the last transaction-log backup successfully restored on the secondary.

Can log shipping use multiple secondary servers?

Yes. One primary database can ship logs to multiple secondary instances. Each secondary has its own copy and restore jobs.

How should applications connect after failover?

Log shipping does not provide a listener. Organisations commonly use a DNS alias, SQL client alias, load balancer configuration or application connection-string change. The redirection method should be tested before production deployment.

Conclusion

SQL Server log shipping remains a practical disaster recovery option for organisations that need a warm standby database, controlled recovery-point intervals and straightforward operational management.

Its effectiveness depends on more than simply creating three SQL Server Agent jobs. File-share security, transaction-log backup ownership, restore latency, server-level object synchronisation, monitoring and tested failover procedures all determine whether the secondary database will be usable during a real incident.

Regular monitoring and scheduled failover tests are essential. An untested secondary database should not be assumed to be a reliable disaster recovery solution.

Need assistance with SQL Server disaster recovery?

Onsys Technologies provides SQL Server health checks, backup and recovery assessments, log-shipping implementation, high-availability design, performance tuning and managed DBA support.

Our database specialists can help assess your recovery objectives, configure and monitor log shipping, test failover and failback procedures, and identify gaps across backups, security, SQL Server Agent jobs and application connectivity.

Contact Onsys Technologies to discuss a SQL Server disaster recovery assessment for your environment.