Upgrading With Log Shipping for a Short Cutover

An upgrade window is too late to begin copying a large database. Upgrading with log shipping lets a newer target receive the full restore and subsequent logs while the old server keeps working. The short final window depends on a healthy chain and a practiced switch.

Confirm the Supported Direction for Upgrading With Log Shipping

A newer SQL Server target can restore backups from a supported older source version. The reverse direction is not supported after the database upgrades. Keep the old source as the rollback route before target writes begin, and decide how those writes would be handled if reversal becomes necessary. Confirm edition, features, and target version before building the shipping plan.

I ask whether the goal is a one-time migration or a continuing disaster recovery configuration. The preparation looks similar, but the post-cutover work differs. Which server owns the final backup chain after the switch? Write that answer into the runbook. A one-way file format change is a poor surprise to discover during rollback. Test the source restart path and client routing before the final window.

Protect the Log Chain

Use the full recovery model and a verified full backup as the base of the target restore. Then take and apply transaction log backups in sequence. If a differential is part of the plan, place it correctly before the later logs. Record the first and last LSN of each file and track copy and restore status. A gap stops the chain.

The query below shows recent log backups from msdb. It helps inspect sequence and timing, but verify that the physical files exist and restore successfully. I check the actual backup chain during rehearsal. A monitor report is useful, but the target restore result is the stronger evidence.

SELECT TOP (30)
    database_name,
    backup_start_date,
    backup_finish_date,
    first_lsn,
    last_lsn
FROM msdb.dbo.backupset
WHERE type = 'L'
ORDER BY backup_finish_date DESC;

Keep the New Target in NORECOVERY

Restore the full backup on the newer instance with NORECOVERY, then restore later log backups with NORECOVERY. The target database cannot serve normal application queries in that state. Do not recover it early for a convenient smoke test, because the log sequence cannot continue after recovery. Use a separate rehearsal copy for application tests.

I label the target clearly as a receiving database so nobody points a client at it prematurely. When the target version is newer, avoid STANDBY for a database that requires upgrade. Use NORECOVERY for this path. The restore state is part of the plan, not an optional flag to adjust during the window.

Monitor Lag Before the Window When Upgrading With Log Shipping

Set a backup, copy, and restore cadence that keeps the target close to the source. Watch for failed jobs, delayed files, disk pressure, and restore backlog. A cutover of minutes is possible only when the final log is small enough to copy and apply in the tested window. Measure the gap in rehearsal and during normal workload, then choose the window from observed behavior.

I check lag before the business freeze, not after. If the secondary is far behind, postpone or repair the pipeline. The final backup should not be asked to carry a backlog hidden by a green job status. Confirm the last restored backup on the target.

Move the Surrounding Instance Work

Upgrading with log shipping moves database changes, not every application dependency. Prepare logins and user mappings, jobs, linked servers, credentials, encryption keys, Database Mail, and server configuration on the newer instance. Validate application connection strings, driver support, and certificate trust. Rehearse with a restored copy that can be recovered and discarded without disturbing the shipping target.

I keep separate checklists for data movement and service readiness. Both must pass. A database can be ready while the application login is missing. Rehearsal is where that ordinary mistake belongs. It has no charm at midnight.

Stop Writes and Send the Final Log

At cutover, quiesce application writes and background jobs on the old server. Take the final log backup under the rehearsed procedure, copy it, and restore it on the new target with NORECOVERY. Confirm that every prior log file has been applied. Only then recover the target. The recovery step upgrades the database for the newer engine and ends the ability to apply further logs from the old chain.

I have the application owner confirm that writes truly stopped. A web screen in maintenance mode can still leave a queue worker active. Record the final source transaction point and backup file. That record helps reconcile the target and decide whether opening traffic is safe.

Switch Clients and Test Work

Change the application endpoint, recycle connection pools, and confirm the new instance from the application session. Validate critical reads, a controlled write, recent records, jobs, and reports. Compare against source data at the final cutoff. Watch query performance because the engine version and later compatibility changes can affect plans. Keep compatibility level changes separate where possible.

The query below identifies the connected target and database. Run it through the same account and host the application uses. I prefer this to trusting a configuration screenshot. The session tells you where it actually landed.

SELECT
    @@SERVERNAME AS ConnectedInstance,
    DB_NAME() AS CurrentDatabase,
    ORIGINAL_LOGIN() AS LoginName,
    SERVERPROPERTY('ProductVersion') AS ProductVersion;

Close Recovery Plans After Upgrading With Log Shipping

After go-live, start the new instance’s backup chain, monitoring, and restore tests. Protect the old source from accidental writes and retain it under the approved rollback period. State when rollback would require moving new target data back by a separate method. Log shipping does not make a newer database restorable on the old engine.

The short cutover when upgrading with log shipping is earned by moving most bytes before users stop. I document actual backup, restore, and client-switch timings from the rehearsal, then compare them with the live run. That evidence is the basis for the next upgrade window, not a blanket promise attached to the word log shipping. Check the first backup from the new primary before closing the migration record.

Related reading on this blog: What are Ports Needed to Configure Log Shipping? Interview Question of the Week #169 and Log Shipping Monitor Not Getting Updated.

Log shipping is not the cutover itself, it is the preparation that makes the final cutover small.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.