Chapter 17 · Replication, Log Shipping, Distributed Availability, and Data Movement
Log Shipping Architecture, Restore Delay, DR Use Cases, and Failover Procedures
Use log shipping as a transparent backup-copy-restore DR pipeline with explicit restore delay, manual failover and measurable RPO/RTO.
Learning outcomes
Replication distributes data subsets. Log shipping instead keeps one or more warm copies of an entire database by repeatedly backing up the primary transaction log, copying each backup file and restoring it on a secondary. It is deliberately simpler than an Availability Group: no listener, no automatic role change, no cluster consensus. That simplicity can be valuable for disaster recovery, delayed-restore protection and version-transition scenarios.
Explain the backup, copy and restore jobs plus optional monitor server as a log-shipping pipeline.
Distinguish NORECOVERY and STANDBY secondaries and reason about intentional restore delay.
Measure RPO from the last restored log rather than from job success alone.
Execute a free single-instance manual log-shipping simulation without overwriting ServiceHubLab.
Plan manual failover/failback including tail-log and client-redirection responsibilities.
1. Log shipping is a scheduled log-chain pipeline
SELECT SERVERPROPERTY('ProductVersion') AS product_version, SERVERPROPERTY('ProductUpdateLevel') AS update_level, SERVERPROPERTY('Edition') AS edition, SERVERPROPERTY('EngineEdition') AS engine_edition, SERVERPROPERTY('IsHadrEnabled') AS hadr_enabled, SERVERPROPERTY('InstanceDefaultBackupPath') AS default_backup_path, SERVERPROPERTY('ServerName') AS server_name;GOSELECT name, compatibility_level, recovery_model_desc, is_published, is_subscribed, is_merge_published, is_distributorFROM sys.databasesWHERE name IN (N'ServiceHubLab', N'master', N'msdb') OR is_published=1 OR is_subscribed=1 OR is_merge_published=1 OR is_distributor=1;GO| Stage | Runs where | Failure symptom |
|---|---|---|
| Backup job | Primary | No new .trn files; primary log can grow if FULL model log backups stop. |
| Copy job | Each secondary | Backups exist at source but are absent/late at secondary staging path. |
| Restore job | Each secondary | Files are copied but last restored time/LSN stops advancing. |
| Monitor/alert job | Monitor or local servers | Threshold breach reveals that backup/restore cadence is outside objective. |
SQL Server Agent normally schedules these jobs, which is why SQL Server 2025 Standard/Enterprise (and their free Developer counterparts) are the practical lab editions. Express does not support the built-in log shipping feature. The primary must use FULL or BULK_LOGGED recovery so transaction-log backups exist.
USE msdb;GOSELECT primary_database, backup_threshold, threshold_alert_enabled, last_backup_date, last_backup_fileFROM dbo.log_shipping_monitor_primary;SELECT secondary_database, restore_delay, restore_modeFROM dbo.log_shipping_secondary_databases;GO2. A same-instance manual lab exposes the real mechanics
The following lab reproduces the essential backup→restore chain on one free Developer instance. It is not the SQL Server Agent log-shipping feature, but every restore rule is real. It uses the instance default backup directory so the path works on Windows or Linux when the SQL Server service can write there.
USE master;GOIF DB_ID(N'ServiceHubLS_Primary') IS NOT NULL DROP DATABASE ServiceHubLS_Primary;IF DB_ID(N'ServiceHubLS_Secondary') IS NOT NULL DROP DATABASE ServiceHubLS_Secondary;GOCREATE DATABASE ServiceHubLS_Primary;ALTER DATABASE ServiceHubLS_Primary SET RECOVERY FULL;GOUSE ServiceHubLS_Primary;CREATE TABLE dbo.CommitMarker(commit_id bigint PRIMARY KEY, note nvarchar(100), committed_at datetime2(3) DEFAULT SYSUTCDATETIME());INSERT dbo.CommitMarker(commit_id,note) VALUES (1,N'baseline');GODECLARE @dir nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));DECLARE @full nvarchar(4000)=CONCAT(@dir, CASE WHEN RIGHT(@dir,1) IN ('/','\\') THEN '' WHEN CHARINDEX('\\',@dir)>0 THEN '\\' ELSE '/' END, 'ServiceHubLS_full.bak');DECLARE @sql nvarchar(max)=N'BACKUP DATABASE ServiceHubLS_Primary TO DISK=@p WITH INIT,CHECKSUM;';EXEC sys.sp_executesql @sql,N'@p nvarchar(4000)',@p=@full;GOBackup/restore file names can collide across repeated labs; run only in a disposable environment. If your instance default backup path is NULL or not writable, choose a service-writable local directory and substitute it deliberately.
USE ServiceHubLS_Primary;INSERT dbo.CommitMarker(commit_id,note) VALUES (2,N'after full'),(3,N'before delay window');GODECLARE @dir nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));DECLARE @log1 nvarchar(4000)=CONCAT(@dir, CASE WHEN RIGHT(@dir,1) IN ('/','\\') THEN '' WHEN CHARINDEX('\\',@dir)>0 THEN '\\' ELSE '/' END, 'ServiceHubLS_001.trn');DECLARE @sql nvarchar(max)=N'BACKUP LOG ServiceHubLS_Primary TO DISK=@p WITH INIT,CHECKSUM;';EXEC sys.sp_executesql @sql,N'@p nvarchar(4000)',@p=@log1;GO
In a real topology the copy job transports this file to the
secondary. In this one-instance lab, restore the full backup to
a different database in NORECOVERY, then apply the
log. The explicit MOVE paths prevent the restore
from colliding with the primary database files.
USE master;GODECLARE @b nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));DECLARE @d nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultDataPath'));DECLARE @l nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultLogPath'));DECLARE @bsep nchar(1)=CASE WHEN CHARINDEX('\',@b)>0 THEN '\' ELSE '/' END;DECLARE @dsep nchar(1)=CASE WHEN CHARINDEX('\',@d)>0 THEN '\' ELSE '/' END;DECLARE @lsep nchar(1)=CASE WHEN CHARINDEX('\',@l)>0 THEN '\' ELSE '/' END;DECLARE @full nvarchar(4000)=CONCAT(@b,CASE WHEN RIGHT(@b,1) IN ('/','\') THEN '' ELSE @bsep END,'ServiceHubLS_full.bak');DECLARE @log1 nvarchar(4000)=CONCAT(@b,CASE WHEN RIGHT(@b,1) IN ('/','\') THEN '' ELSE @bsep END,'ServiceHubLS_001.trn');DECLARE @datafile nvarchar(4000)=CONCAT(@d,CASE WHEN RIGHT(@d,1) IN ('/','\') THEN '' ELSE @dsep END,'ServiceHubLS_Secondary.mdf');DECLARE @logfile nvarchar(4000)=CONCAT(@l,CASE WHEN RIGHT(@l,1) IN ('/','\') THEN '' ELSE @lsep END,'ServiceHubLS_Secondary_log.ldf');DECLARE @q nchar(1)=NCHAR(39);DECLARE @sql nvarchar(max)= N'RESTORE DATABASE ServiceHubLS_Secondary FROM DISK=@full WITH MOVE N''ServiceHubLS_Primary'' TO N' + @q + REPLACE(@datafile,@q,@q+@q) + @q + N', MOVE N''ServiceHubLS_Primary_log'' TO N' + @q + REPLACE(@logfile,@q,@q+@q) + @q + N', NORECOVERY, REPLACE; RESTORE LOG ServiceHubLS_Secondary FROM DISK=@log1 WITH NORECOVERY;';EXEC sys.sp_executesql @sql,N'@full nvarchar(4000),@log1 nvarchar(4000)',@full=@full,@log1=@log1;SELECT name,state_desc FROM sys.databases WHERE name=N'ServiceHubLS_Secondary';GO3. NORECOVERY, STANDBY and delayed restore
NORECOVERY keeps the database non-readable and ready for more log restores. STANDBY rolls back uncommitted work into a standby file and permits limited read-only access between restores; users can be disconnected when the next restore runs. A configured restore delay intentionally keeps the secondary behind. That worsens ordinary RPO but can create a window to recover rows before an accidental change is applied.
| Choice | Benefit | Tradeoff |
|---|---|---|
| NORECOVERY | Simplest continuous restore state | No reads until recovery/failover. |
| STANDBY | Read-only access between restore jobs | Restore cycles can disconnect readers; same-major-version constraints apply in upgrade scenarios. |
| Delayed restore | Protection window against quickly detected logical mistakes | Secondary is intentionally stale; failover RPO includes the unapplied delay. |
The copy job can be current while restore is stopped. Measure last backup, last copied file and last restored file/time/LSN separately. Alert on the business freshness objective, not only job exit codes.
1. Stop application writes to the old primary if it is reachable.2. Take a tail-log backup when possible and appropriate.3. Copy every remaining log backup to the secondary.4. Restore all remaining logs in sequence; recover the secondary only when promotion is authorized.5. Redirect the application/DNS/connection string manually—log shipping has no listener.6. Validate last business commit, integrity, security objects/jobs and backup ownership.7. Rebuild a reverse/failback data-movement plan; do not assume automatic resynchronization.USE master;GOIF DB_ID(N'ServiceHubLS_Secondary') IS NOT NULLBEGIN ALTER DATABASE ServiceHubLS_Secondary SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE ServiceHubLS_Secondary;END;IF DB_ID(N'ServiceHubLS_Primary') IS NOT NULLBEGIN ALTER DATABASE ServiceHubLS_Primary SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE ServiceHubLS_Primary;END;GO-- Backup files are intentionally not deleted by T-SQL. Remove only the lab files-- after confirming the exact paths and that no other restore drill needs them.4. Production judgment
Production judgment. Log shipping is excellent when inexpensive warm DR and operational transparency are more valuable than automatic failover. It also supports restore delay. Its RTO includes detection, operator authorization, remaining-log restore, recovery and client redirection.
Check your understanding
- Why does log shipping require FULL or BULK_LOGGED recovery?
- What does the copy job prove?
- Why would an operator deliberately configure restore delay?
- What does STANDBY provide that NORECOVERY does not?
- Does log shipping redirect applications automatically after promotion?
Review the answers
1. Because the technology moves transaction-log backups; SIMPLE does not support the required log-backup chain.
2. Only that a backup file reached the secondary staging location—not that it was restored or that the secondary meets the RPO.
3. To preserve an older copy long enough to recover from quickly detected accidental changes or logical corruption.
4. Limited read-only access between restore operations, at the cost of reader disruption/standby-file management.
5. No. Failover and application redirection are manual orchestration responsibilities.
Authoritative references
- About log shipping — backup/copy/restore pipeline, delay and SQL Server 2025 TLS behavior
- Add a log shipping secondary — NORECOVERY/STANDBY, delay and Agent schedules
- Monitor log shipping — status/alert responsibilities
- SQL Server 2025 editions and supported features — log-shipping edition support