What Is Log Shipping?

Log Shipping is a built-in SQL Server DR solution that periodically restores transaction log backups onto a Secondary server to keep data in sync.

Simpler to configure than Always On AG (no domain/WSFC required)
Acceptable lag: minutes (15 minutes to 1 hour)
Cost-effective: available on Standard edition
Multiple Secondaries supported

Three SQL Agent jobs

Job	Runs on	Role
Backup	Primary	Creates transaction log backups
Copy	Secondary	Copies backup files locally
Restore	Secondary	Applies log backups to the database

Environment Setup

Server	IP	Role
SQL-Primary	192.168.1.10	Primary DB
SQL-Secondary	192.168.1.11	Secondary (DR)
Monitor server	192.168.1.12	Optional

Shared folder: \\SQL-Primary\LogShipBackup

Step 1 — Set Up the Shared Backup Folder (Primary)

POWERSHELL

# Create folder
New-Item -Path "C:\LogShipBackup" -ItemType Directory

# Share (grant access to the Secondary server computer account)
New-SmbShare -Name "LogShipBackup" -Path "C:\LogShipBackup" `
  -FullAccess "DOMAIN\SQL-Secondary$", "DOMAIN\sql_agent"

Step 2 — Verify Database Recovery Model

SQL

-- On Primary
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'SalesDB';
-- Must be FULL

-- If SIMPLE, change it
ALTER DATABASE SalesDB SET RECOVERY FULL;

-- Full backup (required before initializing Log Shipping)
BACKUP DATABASE SalesDB
  TO DISK = '\\SQL-Primary\LogShipBackup\SalesDB_full.bak'
  WITH FORMAT, COMPRESSION;

Step 3 — Configure Log Shipping in SSMS

SSMS → right-click Primary DB → Properties → Transaction Log Shipping tab
Check Enable this as a primary database in a log shipping configuration
Backup settings:
- Backup folder: C:\LogShipBackup
- Network path: \\SQL-Primary\LogShipBackup
- Backup interval: 15 minutes
- Backup retention period: 72 hours
Add a secondary database:
- Server: SQL-Secondary
- Initialize: restore from a full backup
- Copy interval: 15 minutes
- Restore interval: 15 minutes
- Restore mode: STANDBY (readable) or NORECOVERY (not readable)

Configure Directly with T-SQL (Optional)

Primary — Backup Job

SQL

-- Register Log Shipping Primary
EXEC master.dbo.sp_add_log_shipping_primary_database
    @database = N'SalesDB',
    @backup_directory = N'C:\LogShipBackup',
    @backup_share = N'\\SQL-Primary\LogShipBackup',
    @backup_job_name = N'LSBackup_SalesDB',
    @backup_retention_period = 4320,      -- 72 hours (in minutes)
    @backup_threshold = 60,               -- alert if exceeded by 60 minutes
    @threshold_alert_enabled = 1,
    @history_retention_period = 5760,
    @backup_job_id = @LS_BackupJobID OUTPUT,
    @primary_id = @LS_PrimaryId OUTPUT;

-- SQL Agent backup job schedule (every 15 minutes)
EXEC msdb.dbo.sp_attach_schedule
    @job_name = N'LSBackup_SalesDB',
    @schedule_name = N'LS_15min';

Secondary — Copy and Restore Jobs

SQL

EXEC master.dbo.sp_add_log_shipping_secondary_primary
    @primary_server = N'SQL-Primary',
    @primary_database = N'SalesDB',
    @backup_source_directory = N'\\SQL-Primary\LogShipBackup',
    @backup_destination_directory = N'C:\LogShipRestore',
    @copy_job_name = N'LSCopy_SQL-Primary_SalesDB',
    @restore_job_name = N'LSRestore_SQL-Primary_SalesDB',
    @file_retention_period = 4320,
    @copy_job_id = @LS_CopyJobID OUTPUT,
    @restore_job_id = @LS_RestoreJobID OUTPUT,
    @secondary_id = @LS_SecondaryId OUTPUT;

EXEC master.dbo.sp_add_log_shipping_secondary_database
    @secondary_database = N'SalesDB',
    @primary_server = N'SQL-Primary',
    @primary_database = N'SalesDB',
    @restore_delay = 0,
    @restore_mode = 1,         -- 1: STANDBY (readable), 0: NORECOVERY
    @disconnect_users = 0,
    @block_size = 512,
    @buffer_count = 15,
    @max_transfer_size = 1048576,
    @restore_threshold = 45,   -- alert if exceeded by 45 minutes
    @threshold_alert_enabled = 1,
    @history_retention_period = 5760,
    @secondary_id = @LS_SecondaryId;

Monitoring

SQL

-- Check Log Shipping status on Primary
SELECT
    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;

-- Check restore status on Secondary
SELECT
    secondary_database,
    last_restored_file,
    last_restored_date,
    last_restored_latency,   -- latency in minutes
    restore_threshold
FROM msdb.dbo.log_shipping_monitor_secondary;

Failover Procedure

When Primary fails, promote Secondary to the production server.

SQL

-- On Secondary: restore remaining logs, then RECOVERY
RESTORE DATABASE SalesDB WITH RECOVERY;
-- DB state afterward: ONLINE (writable)

-- Point the application connection string to the SQL-Secondary IP

Log Shipping vs Always On AG

Item	Log Shipping	Always On AG
Edition	Standard supported	Enterprise required
Recovery time	Minutes (RPO ~15 min)	Seconds
Automatic failover	Not available (manual)	Available (synchronous mode)
Domain required	Not required	Required (WSFC)
Configuration complexity	Low	High
Read offload	Available in STANDBY mode	Always available

#mssql#sql-server#log-shipping#dr#backup#recovery

Editorial note

AI tools may assist drafting. Check the evidence in each guide for whether commands were run and which environment was used. Confirm the current docs for your version before applying instructions, and email us about errors. email us

How we write and review →Editorial policy →

Questions & answers

Ask a question about this guide. We'll review and reply.

MSSQL Log Shipping — Disaster Recovery Redundancy Setup