SQL Server Migration with Minimal Downtime: Methods, Cutover Runbook and Rollback

Copying databases to a new server is the easy part of a SQL Server migration. The hard part starts when the system runs 24/7, the databases are measured in terabytes and the business gives you a downtime window of minutes, not hours. Then the real questions are different:

• How do you keep the new server current while production keeps writing to the old one?
• How do you prove that the last transaction reached the target before you switch?
• How do you make sure nobody writes to both servers at the same time?
• Up to which point can you still go back, and what does going back cost after that?

This guide covers the techniques that move most of the data while production is running, how to run the cutover and how to plan the way back. For the version decision itself, start with our SQL Server 2025 upgrade guide; this article goes deeper into the moving part.

Start with the Downtime Budget, Not the Technique

The first decision in a migration is not “backup or Always On”. It is how long the application may be unavailable (the RTO of the cutover) and how much data loss, if any, is acceptable (the RPO). Everything else follows from those two numbers.

If an 8 TB database has to switch over in 15 minutes, copying it on the night is impossible. The bulk of the data has to move days earlier while production runs, and only the last changes move during the window. That makes two measurements essential before you choose a method:

• Database size and network throughput decide how long the initial copy takes.
• Transaction log generation rate decides whether the target can keep up afterwards. If the source produces log faster than the network or the target’s redo can absorb, the gap only grows.

-- Sample the log generation rate per database over 60 seconds
DECLARE @t1 TABLE (db sysname, bytes bigint);
INSERT @t1 SELECT instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log Bytes Flushed/sec' AND instance_name <> '_Total';

WAITFOR DELAY '00:01:00';

SELECT p.instance_name AS database_name,
       (p.cntr_value - t.bytes) / 60 / 1024 AS avg_log_kb_per_sec
FROM sys.dm_os_performance_counters AS p
JOIN @t1 AS t ON t.db = p.instance_name
WHERE p.counter_name = 'Log Bytes Flushed/sec'
ORDER BY avg_log_kb_per_sec DESC;

Measure it at peak times, month-end and during index maintenance, not on a quiet afternoon. Record CPU, memory, disk latency and the duration of critical queries at the same time; without that baseline you cannot tell later whether the new server is actually better.

Inventory Everything That Is Not in the Database

Databases move with the chosen technique. Almost everything that breaks on the first morning lives outside them. Before the migration, list:

• Logins with their SIDs, server roles and the mapping to database users
• SQL Server Agent jobs, schedules, owners, proxies and credentials
• Linked servers, providers and login mappings (see our linked server guide)
• SSIS catalog, Reporting Services, Database Mail and maintenance plans
• TDE certificates, database master keys and backup encryption certificates
• Service Broker, replication and CDC configurations
• CLR assemblies and anything that calls xp_cmdshell
• Jobs that run outside SQL Server, such as Windows Task Scheduler tasks, together with file shares and service accounts
• Connection strings, DNS aliases, SPNs and TLS certificates

The upgrade guide has the full table of objects that live outside the user databases.

Choosing the Method

Backup Chain with a Tail-Log Backup

For most systems with a downtime window of up to an hour or two, this is still the simplest and most reliable method. The idea is to restore almost everything in advance and keep only the last log backup for the cutover night.

1. Days before: restore a full backup on the target WITH NORECOVERY.
2. Closer to the date: restore a differential and then the regular log backups, all WITH NORECOVERY, so the target stays only minutes behind.
3. Cutover: stop the application, take a tail-log backup with NORECOVERY on the source, restore it on the target and recover.

-- On the source, at cutover: the tail-log backup puts the source database
-- into RESTORING state, so nothing can write to it any more
BACKUP LOG SalesDB
    TO DISK = N'X:\Migration\SalesDB_tail.trn'
    WITH NORECOVERY, CHECKSUM, INIT;

-- On the target: apply the tail and bring the database online
RESTORE LOG SalesDB
    FROM DISK = N'X:\Migration\SalesDB_tail.trn'
    WITH NORECOVERY, CHECKSUM;
RESTORE DATABASE SalesDB WITH RECOVERY;

The tail-log backup with NORECOVERY does two jobs at once. It is a built-in write freeze, because the source database can no longer accept changes. It also keeps your way back simple: until the target goes live, RESTORE DATABASE SalesDB WITH RECOVERY on the source brings the old database back online with no data lost.

Restore under the final database name, so you don’t need to rename anything after the cutover; if you must, read how to rename a database safely first. If you change file locations with MOVE, keep the logical file names consistent; mismatched file metadata is one of the causes behind error 5161. Our restore guide covers the RESTORE options in detail.

Log Shipping

Log shipping automates the same chain with Agent jobs: back up the log on the source, copy it, restore it on the target. It is a good fit when the cutover can wait for one last log backup and the team already knows the technology. It works from a lower to a higher version, which makes it a common upgrade path.

One limitation catches people out: when the target runs a higher version, the secondary database cannot be kept in STANDBY (read-only) mode, because opening it read-only would require upgrading it. It stays in NORECOVERY until cutover, so you cannot query it to validate data in advance. Plan validation on a separate restored copy instead.

Distributed Availability Groups

A distributed availability group links two separate availability groups, each with its own cluster and listener. The primary replica of the source AG (the global primary) sends log records to the primary replica of the target AG (the forwarder), which passes them on to its own secondaries. Production keeps writing to the source while the target, possibly in another data center, on new hardware or a new Windows version, stays current.

This makes it one of the strongest options for large, critical databases. The conditions matter, so check them before you design around it:

• Versions: SQL Server 2017 and later can mix major versions. The AG holding the writable primary must be the same or a lower version than the target. A SQL Server 2016 AG can be the source; AGs on SQL Server 2012 or 2014 cannot take part at all.
• Edition: plan for Enterprise edition. Standard edition is supported only in specific scenarios connected to Azure services.
• No listener of its own: each underlying AG has a listener; the distributed AG does not. Applications are moved by renaming or re-creating the listener, or by pointing them at the target AG’s listener.
• Manual failover only, and only with FORCE_FAILOVER_ALLOW_DATA_LOSS. No data is lost if you synchronize first, as described below.

Migrating to a higher version

When the target AG runs a newer SQL Server version than the source, Microsoft documents several limitations:

• Automatic seeding is not supported. Set the seeding mode to MANUAL, take a full and a log backup on the global primary and restore both WITH NORECOVERY on the forwarder. Otherwise the migration fails with error 946.
• The target is not readable while the source is on the lower version. The secondary AG shows as Partially Healthy and its databases as Synchronizing / In Recovery, even in synchronous commit.
• There is no failback. After the failover to the higher version, changes no longer flow back to the old AG, and you cannot fail back to it unless you upgrade it to the same version.

Failing over without data loss

On SQL Server 2022 and later, the distributed AG supports REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT, which is designed to guarantee no data loss during the failover. The condensed sequence:

-- 1. On the global primary AND the forwarder: synchronous commit for the distributed AG
--    (the local AGs on both sides must also be in synchronous commit)
ALTER AVAILABILITY GROUP [distributedAG] MODIFY AVAILABILITY GROUP ON
    'ag1' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT),
    'ag2' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

-- 2. Wait until synchronization_state_desc = SYNCHRONIZED, then on the global primary:
ALTER AVAILABILITY GROUP [distributedAG] SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);

-- 3. On the global primary: give up the primary role (the distributed AG is now unavailable)
ALTER AVAILABILITY GROUP [distributedAG] SET (ROLE = SECONDARY);

-- 4. On the forwarder: take over
ALTER AVAILABILITY GROUP [distributedAG] FORCE_FAILOVER_ALLOW_DATA_LOSS;

-- 5. On the new forwarder (the old global primary):
ALTER AVAILABILITY GROUP [distributedAG] SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0);

On SQL Server 2019 and earlier, the setting is not available for distributed AGs. There you stop all transactions on the global primary, switch to synchronous commit, and fail over only when last_hardened_lsn is identical for every database on both the global primary and the forwarder. If the values do not match, you fail back to the global primary and start again.

The health of both sides is visible only through the DMVs, not in the Management Studio dashboard:

SELECT ag.name AS ag_name, ag.is_distributed,
       ar.replica_server_name AS replica_or_underlying_ag,
       DB_NAME(drs.database_id) AS database_name,
       drs.synchronization_state_desc, drs.synchronization_health_desc,
       drs.log_send_queue_size, drs.redo_queue_size,
       drs.last_hardened_lsn, drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_groups   AS ag ON ag.group_id   = drs.group_id
JOIN sys.availability_replicas AS ar ON ar.replica_id = drs.replica_id
ORDER BY ag.name, database_name;

Switching to synchronous commit adds the network round trip to every commit. Test its effect on transaction latency during the dry run, not on the cutover night. SQL Server 2025 also allows a contained availability group to take part in a distributed AG, which carries logins and Agent jobs that live in the AG’s own system databases; check its prerequisites before relying on it.

Transactional Replication

Transactional replication reads committed changes from the source log and applies them, in order, to a subscriber. It needs no cluster and can move only the tables you choose, which is useful when the source has no availability groups or when only part of a database moves.

It is not a full database migration method, and its limits decide whether it fits:

• Every published table needs a primary key.
• Scope: tables outside the publication, server-level objects, Agent jobs and permissions do not move.
• Schema changes: some DDL replicates, but not all of it; agree on a schema freeze for the migration period.
• Versions: the publisher, distributor and subscriber combination must be within Microsoft’s replication compatibility matrix.
• With distributed AGs: the forwarder replica cannot be configured as a publisher.
• The subscriber must stay read-only for the application until cutover; independent writes there break consistency.

At cutover, low latency on the monitor is not enough. Stop writes on the source, let open transactions finish, then post a tracer token and confirm it reached the subscriber:

-- On the publisher, in the published database, after the write freeze
DECLARE @token_id int;
EXEC sys.sp_posttracertoken
     @publication = N'SalesPublication',
     @tracer_token_id = @token_id OUTPUT;

-- Repeat until subscriber latency is filled in for every subscriber
EXEC sys.sp_helptracertokenhistory
     @publication = N'SalesPublication',
     @tracer_id = @token_id;

A token that has not arrived yet shows empty latency; that means “not yet”, not “done”. Only when the token has reached every subscriber do you know that everything written before it is there too.

Third-Party CDC Tools

Tools such as Oracle GoldenGate or AWS Database Migration Service combine an initial load with continuous change capture. They make sense mainly for cross-platform moves or when data has to be filtered and transformed on the way. For SQL Server to SQL Server they add licensing and a second technology to operate. Check the vendor’s list of supported data types and operations for your version; DDL changes, data type coverage and log retention on the source are the usual gaps. Replication lag is never guaranteed to be zero, so the cutover still needs a write freeze and a reconciliation step.

The Cutover Runbook

A runbook is not just a list of commands. It defines the conditions under which you move to the next step and who decides.

1. Discovery and assessment: inventory, versions and features in use, RTO/RPO, data size and log rate, chosen method.
2. Initial synchronization: target built from an installation checklist, seeding or initial load, sync health monitored for days.
3. Dry run: the full cutover rehearsed on copies, timed step by step, including the rollback.
4. Go/no-go: sync healthy, validation passed, application owners available, rollback owner named.
5. Write freeze: stop the applications, disable the application logins on the source and wait for open transactions to finish.
6. Final sync and proof: tail-log restored, hardened LSNs equal or tracer token arrived.
7. Switch: target writable, listener or DNS alias moved, connection strings changed.
8. Acceptance: smoke tests and business sign-off within an agreed time.

Rollback: Decide It Before You Start

How easily you can go back depends on the moment:

• Before the target accepts writes, going back is cheap: cancel the switch and keep using the source. With the tail-log method, recover the source database; with a distributed AG, the source is still intact.
• After the target accepts writes, going back means losing those writes, re-entering them or running a separate reverse synchronization. After a move to a higher version, backups cannot be restored on the old version and a distributed AG cannot fail back.

Agree on the go/no-go moment, write down who can trigger the rollback and test it in the dry run. We cover the same trap for version upgrades in our SQL Server 2016 end-of-support guide.

The First Seven Days

• First 24 hours: connection errors and failed logins in the error log, backups running on the new server, critical jobs, blocking.
• First 72 hours: plan regressions in Query Store, log and tempdb growth, deadlocks, integrations and linked servers.
• First week: weekly jobs and reports, a restore test from the new backups and a comparison with the baseline.

If the new host was renamed to take over the old server name, check @@SERVERNAME; replication, some jobs and monitoring tools rely on it. Our guide on fixing @@SERVERNAME after a rename shows how. Keep the databases at their old compatibility level until the system is stable, then raise it deliberately using the compatibility level guide.

How Aryasoft Does It

Aryasoft’s senior DBA team measures log generation, data size and network throughput before choosing the method, and builds the inventory of everything that lives outside the databases. Every migration is rehearsed end to end on copies of production, including the rollback, and the cutover follows a written runbook with a named go/no-go owner. After the switch, the team stays with the system through the first week of real workload.