Series: SharePoint 2019 → Subscription Edition Migration — Post #6 of 12
← Post #5: Testing Port Connectivity and Firewall Rules for SharePoint SE | Post #7: Parallel Database Backup and Restore at Scale →

If your SharePoint 2019 farm has fifty content databases averaging 200 GB each, a conventional backup-and-restore migration will take the better part of a business day just to move data — before SharePoint even starts. SQL Server log shipping eliminates that problem. Instead of a single monolithic restore at cutover, the secondary SQL instance (your future SharePoint Subscription Edition SQL server) stays continuously synchronized with the primary throughout the migration project. By the time you flip the switch, the gap between primary and secondary is minutes, not hours. Cutover shrinks from a multi-hour outage to a controlled, predictable window.

This post covers the full log shipping lifecycle for a SharePoint 2019 → SPSE migration: how to configure log shipping across all content databases using a CSV-driven PowerShell script, how to monitor health, how to simulate and repair a log chain break before production, and how to cleanly disable and decommission log shipping after go-live.

Why SQL Log Shipping Is the Right Choice for SharePoint Migration

The problem with backup-and-restore for large SharePoint farms

Backup-and-restore works well when your databases are small or your tolerance for downtime is high. For production SharePoint farms, neither condition usually applies.

A full restore of a 500 GB content database takes two to four hours, depending on hardware. Scale that across twenty or thirty content databases and the math becomes uncomfortable. Even with parallel restores, the secondary is only a snapshot of the primary at one point in time. Every minute users keep working on the primary after that snapshot widens the gap. The final cutover requires taking SharePoint offline long enough to apply all the changes that accumulated since the last restore — and by then, “a few hours” of downtime is already baked in.

What log shipping gives you instead

SQL Server log shipping keeps the secondary continuously updated with transaction log backups from the primary. The process runs automatically through SQL Agent jobs. The secondary database stays in RESTORING or STANDBY state and absorbs log backups in near-real-time throughout the migration preparation window — which might span weeks.

When it is time to cut over, the restore latency is typically minutes. You disable log shipping, apply any final pending logs, recover the databases, and hand them to SharePoint Subscription Edition. The heavy lifting was already done.

How this fits the SP2019 → SPSE migration architecture

Log shipping sits in the middle of the migration timeline. Post #5 covered infrastructure readiness: port connectivity, firewall rules, and network path validation. This post builds on that by establishing the SQL-level data pipeline between your SP2019 SQL instance (primary) and your SPSE SQL instance (secondary). Post #7 picks up with parallel backup-and-restore for databases that are not suited for log shipping (for example, SharePoint’s configuration database or service application databases where the log chain is not practical to maintain).

The result is a tiered approach: log shipping handles content databases continuously; parallel restore handles everything else in a single seeding pass.

Note: Log shipping requires all content databases to be in FULL recovery model. If any database is in Simple recovery, the transaction log is not preserved between checkpoints and log shipping will fail. Verify recovery models across your entire farm before proceeding.

How SQL Server Log Shipping Works (for SharePoint DBAs)

The three SQL Agent jobs per database

Log shipping is implemented as three SQL Server Agent jobs per database. Understanding each job’s role is essential before you configure anything.

Backup job — runs on the primary SQL instance. On a defined schedule, it takes a transaction log backup of the source database and writes the .trn file to a shared UNC path accessible by both the primary and secondary SQL Agent service accounts.

Copy job — runs on the secondary SQL instance. On its own schedule, it polls the shared backup path and copies new .trn files to a local staging directory on the secondary server. This separates the network copy operation from the restore operation, which keeps the restore job predictable even if network throughput varies.

Restore job — runs on the secondary SQL instance. On its schedule, it applies any copied .trn files to the secondary database, in strict LSN (Log Sequence Number) order. This is what keeps the secondary synchronized.

The naming convention used by Configure-LogShippingJobs.ps1 follows the standard pattern:

• LogShip_Backup_<DatabaseName> — on the primary
• LogShip_Copy_<DatabaseName> — on the secondary
• LogShip_Restore_<DatabaseName> — on the secondary

Recovery modes explained

The restore job applies log backups in one of two modes:

NORECOVERY — the secondary database remains in a permanent restoring state. It cannot be queried or accessed. This is the default and the most common choice for migration scenarios where you do not need to read from the secondary.

STANDBY — the secondary is readable between restore cycles. After each log backup is applied, the database is briefly available for read-only queries, then returned to standby before the next restore. Useful for monitoring data integrity on the secondary without interrupting the log shipping chain.

For most SharePoint migrations, NORECOVERY is sufficient. Use STANDBY mode (via the StandbyMode column in the CSV) if your DBA team wants to run read-only validation queries against the secondary during the pre-cutover period.

Log chain integrity

Every transaction log backup must be applied to the secondary in the exact order it was taken, with no gaps in the LSN sequence. This unbroken sequence is the log chain. A gap — caused by a missed backup, a backup taken outside of log shipping’s control, or a database temporarily switched to Simple recovery — breaks the chain. The restore job will fail, and log shipping halts until the chain is repaired.

This is not a theoretical concern. In real migrations, log chain breaks happen: a disk fills up, a scheduled maintenance task takes a log backup, someone changes a recovery model. The Simulate-ChainBreak.ps1 and Repair-ChainWithDiff.ps1 scripts exist precisely so you practice detection and repair before it happens in a production window.

Prerequisites Before You Configure Log Shipping

SQL Server requirements

SharePoint-specific prerequisites

Before running the configuration script, compile the definitive list of databases that need log shipping. Query the primary SQL instance directly — do not rely on Central Administration alone:

-- Get all SharePoint databases from the primary SQL instance
SELECT name, recovery_model_desc, state_desc
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY name;

This list becomes the input for your CSV schedule file. Every database in this list that is in FULL recovery and needs to be available on the SPSE SQL instance after cutover should have a row in the CSV.

Also confirm the SharePoint farm is not in the middle of a large batch operation (overnight timer job, search crawl seed) when you start the initial seeding. The first log backup after a full backup can be large if there has been heavy write activity.

Note: The production Configure-LogShippingJobs.ps1 script handles all prerequisite edge cases automatically — service account validation, path access checks, and recovery model enforcement are built into the pre-flight checks. See the SQL Migration Bundle for the full 216-line version.

Preparing the CSV Schedule File for Configure-LogShippingJobs.ps1

Why the script is CSV-driven

Real SharePoint farms have anywhere from a dozen to over a hundred content databases. Configuring log shipping by hand — writing T-SQL for each database, creating three SQL Agent jobs each time — is error-prone and not repeatable. The CSV approach separates configuration from code: one file defines what gets shipped and when, and the script reads it line-by-line.

This also means your log shipping configuration is version-controlled, auditable, and easy to update if schedules need to change.

CSV schema — columns explained

Important: Keep RestoreSchedule at least 5–10 minutes ahead of BackupSchedule. If the restore job runs at the same interval as the backup job, it may fire before the copy job has finished moving the latest file.

Example CSV

DatabaseName,BackupPath,CopyPath,RestorePath,BackupSchedule,CopySchedule,RestoreSchedule,SecondaryServer,StandbyMode
SP_Content_Intranet,\\sql-sec\logship\backups\,D:\LogShip\Copy\,D:\LogShip\Restore\,15,15,20,SQL-SPSE-01,YES
SP_Content_HR,\\sql-sec\logship\backups\,D:\LogShip\Copy\,D:\LogShip\Restore\,15,15,20,SQL-SPSE-01,YES
SP_Content_Finance,\\sql-sec\logship\backups\,D:\LogShip\Copy\,D:\LogShip\Restore\,15,15,20,SQL-SPSE-01,NO
WSS_Content,\\sql-sec\logship\backups\,D:\LogShip\Copy\,D:\LogShip\Restore\,30,30,35,SQL-SPSE-01,YES
SP_Content_Projects,\\sql-sec\logship\backups\,D:\LogShip\Copy\,D:\LogShip\Restore\,15,15,20,SQL-SPSE-01,YES

• Use the exact database name from sys.databases — not the display name from Central Administration. These can differ.
• Databases with very high write volume (active document libraries, large My Sites DBs) should use shorter intervals (15 minutes or less). Lower-activity databases can use 30-minute intervals to reduce SQL Agent job overhead.
• You can use different BackupPath values per database if you want to distribute log files across multiple shares. The script supports this.

Note: The full production script supports additional scheduling parameters including day-of-week overrides, backup retention window (auto-purge old .trn files), and alerting thresholds not shown in this simplified schema. Full parameter documentation is included in the SQL Migration Bundle.

Running Configure-LogShippingJobs.ps1

What the script does internally

The script reads the CSV file row by row. For each database, it:

1. Validates connectivity to the primary and secondary SQL instances
2. Confirms the database exists on the primary in FULL recovery model
3. Generates T-SQL to create and configure the three SQL Agent jobs (backup, copy, restore) using the schedule and path values from the CSV row
4. Executes the T-SQL via sqlcmd against the appropriate instance (backup job on primary; copy and restore jobs on secondary)
5. Enables each job and writes a confirmation entry to the execution log

The script emits all generated T-SQL to the console before executing, giving you a full audit trail of exactly what was created.

Running the script

.\Configure-LogShippingJobs.ps1 `
  -ConfigFile "C:\Migration\logship-config.csv" `
  -PrimaryServer "SQL-SP2019-01" `
  -SqlCredential (Get-Credential)

The -SqlCredential parameter accepts a PSCredential object. If you are running the script from a session already authenticated as a sysadmin-equivalent account on both instances, you can omit -SqlCredential and the script will use Windows integrated authentication.

For large environments, expect the script to take 2–5 minutes per database depending on SQL Agent job creation overhead. For fifty databases, allow 3–5 hours for the initial log backup and seeding cycle to complete.

What gets created

After the script completes, verify the following on both SQL instances:

On the primary (SQL-SP2019-01):

• One SQL Agent job per database: LogShip_Backup_<DatabaseName>
• Jobs are enabled and scheduled immediately
• First backup files appear in BackupPath within one backup cycle

On the secondary (SQL-SPSE-01):

• One copy job per database: LogShip_Copy_<DatabaseName>
• One restore job per database: LogShip_Restore_<DatabaseName>
• Copy and restore jobs begin executing after the first backup file arrives

To quickly confirm job creation:

-- Verify log shipping jobs exist on primary
SELECT name, enabled, date_created
FROM msdb.dbo.sysjobs
WHERE name LIKE 'LogShip_Backup_%'
ORDER BY name;

Monitoring Log Shipping Health

Key metrics to track

Once log shipping is running, you need visibility into three things:

• Backup latency — how long ago did the last backup job run successfully on the primary
• Restore latency — how far behind is the secondary database compared to the primary
• Job failure history — which jobs have failed and when

A restore latency of less than 30 minutes is generally acceptable during steady-state migration. As you approach the cutover window, bring that latency down to near-zero before disabling log shipping.

Monitoring queries

The following query gives a real-time view of restore health across all log-shipped databases on the secondary. Run this on the secondary SQL instance:

-- Log shipping restore health — run on secondary (SQL-SPSE-01)
SELECT
    secondary_database,
    primary_server,
    primary_database,
    last_copied_file,
    last_copied_date,
    last_restored_file,
    last_restored_date,
    restore_threshold,
    last_restored_latency
FROM msdb.dbo.log_shipping_monitor_secondary
ORDER BY last_restored_latency DESC;

For a combined view from the primary, including backup history alongside restore status:

-- Combined primary-side log shipping status — run on primary (SQL-SP2019-01)
SELECT
    p.primary_database,
    p.last_backup_file,
    p.last_backup_date,
    s.secondary_server,
    s.secondary_database,
    s.last_restored_date,
    s.last_restored_latency,
    s.restore_threshold
FROM msdb.dbo.log_shipping_primary_databases p
JOIN msdb.dbo.log_shipping_primary_secondaries ps
    ON p.primary_id = ps.primary_id
JOIN msdb.dbo.log_shipping_monitor_secondary s
    ON s.primary_database = p.primary_database
ORDER BY s.last_restored_latency DESC;

For recent SQL Agent job execution history across all log shipping jobs:

-- SQL Agent job history for all log shipping jobs
SELECT
    j.name        AS JobName,
    h.run_date,
    h.run_time,
    CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'In Progress'
    END           AS RunStatus,
    h.message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE j.name LIKE 'LogShip_%'
ORDER BY h.run_date DESC, h.run_time DESC;

Alert thresholds

Configure alerts on the secondary to notify you when restore latency exceeds acceptable bounds:

• Backup threshold: alert if no backup has occurred in more than 30 minutes. Configure this in the backup_threshold column of msdb.dbo.log_shipping_secondary.
• Restore threshold: alert if restore latency exceeds 45 minutes. Set restore_threshold in msdb.dbo.log_shipping_secondary for each monitored database.
• SQL Server Agent can raise alerts based on these thresholds — configure alert notifications in SQL Server Agent → Alerts for event ID 14420 (backup threshold) and 14421 (restore threshold).

Simulating and Repairing a Log Chain Break

Why you must rehearse this before production

Log chain breaks will happen. Disk volumes fill unexpectedly. A maintenance script takes a log backup outside of the log shipping framework. Someone temporarily changes a content database to Simple recovery and back. These are not edge cases — they are normal operational events on a busy farm.

If you encounter a chain break for the first time during the final cutover window, you are looking at a stressful manual repair under time pressure. The correct approach is to deliberately trigger a break on a non-production clone, repair it using Repair-ChainWithDiff.ps1, and confirm that monitoring alerts fired as expected. Run this rehearsal at least once before go-live.

Simulating a break with Simulate-ChainBreak.ps1

Simulate-ChainBreak.ps1 introduces a controlled chain break by taking a transaction log backup of the specified database outside the log shipping monitor’s knowledge. This advances the LSN sequence on the primary without a corresponding entry in the secondary’s restore history. The next time the restore job runs, it detects the LSN gap and fails.

# Simulate a chain break — USE ON TEST ENVIRONMENT ONLY
.\Simulate-ChainBreak.ps1 `
  -DatabaseName "SP_Content_Intranet" `
  -PrimaryServer "SQL-SP2019-01" `
  -BackupPath "C:\Temp\simulate-break\"

Note: Never run Simulate-ChainBreak.ps1 against a database that is part of a live migration target. It will deliberately break log shipping for that database and require a repair cycle to recover. The script includes a -WhatIf parameter to preview the action without executing.

After running the simulation, confirm that:

• The restore job for SP_Content_Intranet fails with a chain break error
• The monitoring query above shows last_restored_latency growing
• SQL Server Agent alert fires (if configured)

Repairing the break with Repair-ChainWithDiff.ps1

A differential backup is the correct repair path after a chain break — not a new full backup. A differential captures all changes since the last full backup and is substantially smaller and faster than a full backup for large databases. The script handles the full repair sequence automatically:

1. Takes a differential backup of the primary database
2. Copies the differential to the secondary
3. Restores the differential to the secondary (re-establishing a valid log base)
4. Re-enables the restore job and resumes the log shipping chain from the differential restore point

# Repair a broken log chain for a single database
.\Repair-ChainWithDiff.ps1 `
  -DatabaseName "SP_Content_Intranet" `
  -PrimaryServer "SQL-SP2019-01" `
  -SecondaryServer "SQL-SPSE-01" `
  -BackupPath "\\\\sql-sec\\logship\\backups\\" `
  -RestorePath "D:\\LogShip\\Restore\\"

After the repair completes, run the monitoring query again and confirm restore latency is decreasing. The restore job should pick up the log chain from the differential restore point and continue applying subsequent logs normally.

Disabling Log Shipping During the Cutover Window

When to disable

Disabling log shipping is a step in the cutover execution sequence, not a standalone operation. The correct sequence, coordinated with Post #8 (Cutover Playbook), is:

1. Confirm restore latency is near-zero (run the monitoring query — latency should be under 5 minutes)
2. Set SharePoint sites to read-only (Post #8 — Set-SitesReadOnly.ps1) to stop new write activity on the primary
3. Wait for any in-flight transactions to flush and the final log backup to complete and restore on the secondary
4. Disable log shipping jobs on both primary and secondary

Disabling log shipping before confirming near-zero latency means you will have data on the primary that has not been applied to the secondary. That gap becomes manual work during the cutover window.

What Disable-LogShippingJobs.ps1 does

The script disables all log shipping SQL Agent jobs from the CSV for both the primary and secondary instances. It does not delete the jobs. The jobs remain in SQL Agent in a disabled state, giving you a rollback path if the cutover is aborted. The job configuration, schedule, and history are fully preserved.

After disabling, secondary databases are still in RESTORING or STANDBY state. They are not yet accessible to SharePoint Subscription Edition. Recovery (bringing databases online) is handled by Bring-DatabasesOnline.ps1, which is covered in Post #8.

Running the disable script

.\Disable-LogShippingJobs.ps1 `
  -ConfigFile "C:\Migration\logship-config.csv" `
  -PrimaryServer "SQL-SP2019-01" `
  -SecondaryServer "SQL-SPSE-01"

The script logs each job state change to the console and to a timestamped log file in the same directory. This creates an audit trail for the cutover change record.

Decommissioning Log Shipping After a Successful Cutover

When to decommission

Decommission is not the same as disable. Disable is a reversible step during the cutover window. Decommission is final.

Wait at least 24–72 hours after go-live before decommissioning. During that window, the disabled log shipping jobs on both instances serve as a fallback reference: if a critical issue surfaces post-cutover, you can still analyze what was in the log chain. After post-migration validation (Post #11) passes and the SPSE farm is confirmed stable, decommission.

What Decommission-LogShipping.ps1 does

• Removes all log shipping SQL Agent jobs (LogShip_Backup_*, LogShip_Copy_*, LogShip_Restore_*) from both primary and secondary
• Drops log shipping configuration metadata from msdb.dbo.log_shipping_primary_databases, msdb.dbo.log_shipping_secondary_databases, and associated monitor tables
• Optionally purges accumulated .trn backup files from the shared path (controlled by -PurgeBackupFiles)
• Writes a decommission summary log with job names, removal timestamps, and disk space recovered if purging

Running the decommission script

.\Decommission-LogShipping.ps1 `
  -ConfigFile "C:\Migration\logship-config.csv" `
  -PrimaryServer "SQL-SP2019-01" `
  -SecondaryServer "SQL-SPSE-01" `
  -PurgeBackupFiles $true

Set -PurgeBackupFiles $false if you want to retain the backup files for archival purposes before decommissioning. The shared path can accumulate gigabytes of .trn files over a multi-week migration; reclaiming that space is usually worthwhile once go-live is confirmed stable.

Log Shipping Phase Reference

The table below summarizes the full log shipping lifecycle across all five scripts. Use it as a quick reference during migration execution.

Get the Complete SQL Migration Bundle

Configure log shipping for 50+ SharePoint databases in minutes, not days

Writing log shipping configuration T-SQL by hand for even a dozen databases is several hours of work. For a large farm with fifty or more content databases, it is effectively a multi-day DBA project — before you have run a single validation query.

The SQL Migration Bundle gives you production-ready scripts for the full log shipping lifecycle, plus the parallel backup-and-restore scripts from Post #7, database recovery, and availability group onboarding from Post #9.

What’s included in the SQL Migration Bundle:

Every script includes a parameter-documented README, is tested against SP2019 → SPSE environments, and handles the edge cases that simplified blog snippets skip.

Contact sudharsan_1985@live.in to get the SQL Migration Bundle.

What’s Next in the Series

Log shipping handles the continuous data synchronization layer. The next post picks up at the restore side: when you need to move databases that are not suited for log shipping — or when you need to seed the secondary quickly before enabling log shipping — you need parallel backup and restore at scale.

Post #7: Parallel Database Backup and Restore at Scale for SharePoint Migration → covers Parallel-Migrate-Full.ps1 and Direct-ParallelBackup.ps1 — how to back up and restore dozens of content databases concurrently using PowerShell runspaces, with throttle control and progress reporting.

If you are joining the series mid-way, Post #1: The Complete Guide to Migrating SharePoint 2019 to Subscription Edition provides the full migration architecture and shows how each post fits into the overall playbook.

The SQL Migration Bundle includes all scripts from both Post #6 and Post #7 — log shipping setup plus parallel restore — covering the two primary mechanisms for getting SharePoint data from SP2019 SQL to SPSE SQL.

Series: SharePoint 2019 → Subscription Edition Migration — Post #6 of 12
← Post #5: Testing Port Connectivity and Firewall Rules for SharePoint SE | Post #7: Parallel Database Backup and Restore at Scale →