Migration Runbook Rollback Points for SQL Server Upgrades
You’re writing the change request for a move from SQL Server 2019 to SQL Server 2025, and you reach the rollback section. The obvious line is “restore the pre-upgrade backup on the old server.” That works until the new server has taken writes you can’t afford to lose. Some parts of the upgrade can’t be reversed at all.

I wanted each rollback step in my runbook to rest on something I had tested, so I tested the directions that matter on two local instances. This post covers what can and can’t go back, and ends with the runbook I’d use. The planning work before this point is in Migration Planning: Compatibility Checks and Deprecated Features, and the checks after it are in Post-Migration Validation with AI-Generated Test Scripts.
The Test Setup
I used two instances on one workstation: SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer). Each test used a small scratch database with one table, [dbo].[orders], and a few rows that record where each row was written.
I tracked two different version numbers. The compatibility level is a per-database setting that controls query processing and some Transact-SQL behaviour.[1] The other is the value DATABASEPROPERTYEX(name, 'Version') returns. Microsoft Learn describes it as the internal version number of the SQL Server code with which the database was created, says it returns NULL when the database hasn’t started, and marks it as informational only and not supported.[2] What I saw doesn’t fit that description well. A 2019 database restored on 2025 reported 904 while it sat in RESTORING, and 998 after the upgrade. I used the property here because the number changed at the point the upgrade ran.
|
1 2 3 4 5 6 7 8 9 10 11 |
USE [master]; SELECT [d].[name] , [d].[state_desc] , [d].[compatibility_level] , DATABASEPROPERTYEX([d].[name], 'Version') AS [internal_version] FROM [sys].[databases] AS [d] WHERE [d].[name] LIKE N'sat[_]mig%'; |
A new database on the 2025 instance reported internal version 998 and compatibility level 170. A new database on the 2019 instance reported internal version 904 and compatibility level 130. It got 130 because model on that instance is at 130, and new databases take the lower of the engine default and the model level.[1]
A 2025 Backup Doesn’t Restore on 2019
I backed up the 2025 database and tried to restore it on 2019. RESTORE HEADERONLY on the 2019 instance read the file without complaint. It returned DatabaseVersion 998, SoftwareVersionMajor 17 and CompatibilityLevel 170, so you can inspect a newer backup from an older server. Proving the Restore, Part 2: HEADERONLY Changes Shape Between Versions covers how that output differs between versions. The restore itself failed:
|
1 2 3 4 |
Msg 3169, Level 16, State 1, Server SQLWS01\SQL2019, Line 1 The database was backed up on a server running version 17.00.5015. That version is incompatible with this server, which is running version 15.00.4500. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. Msg 3013, Level 16, State 1, Server SQLWS01\SQL2019, Line 1 RESTORE DATABASE is terminating abnormally. |
No database was created. This matches the documentation, which says SQL Server backups can’t be restored by any earlier version.[3]
That backup was at level 170, which 2019 doesn’t support. To see whether a lower level changes the result, I created a second database on 2025, set it to compatibility level 130, which 2019 does support,[1] and backed it up. On 2019, RESTORE HEADERONLY reported CompatibilityLevel 130 and DatabaseVersion 998. The restore failed with the same Msg 3169 and Msg 3013, and no database was created. The error names the server version, and the lower level made no difference.
A 2019 Backup Restores on 2025, and the Database Is Upgraded
The forward direction works. I put the 2019 database in the full recovery model, took a full backup, inserted a second row and took a log backup. On 2025 I restored the full backup WITH NORECOVERY, which is how log shipping seeds a secondary:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE [master]; RESTORE DATABASE [sat_mig_runbook_up] FROM DISK = N'C:\backup\sat_mig_runbook_2019.bak' WITH MOVE N'sat_mig_runbook' TO N'D:\data\sat_mig_runbook_up.mdf' , MOVE N'sat_mig_runbook_log' TO N'L:\log\sat_mig_runbook_up_log.ldf' , NORECOVERY; RESTORE LOG [sat_mig_runbook_up] FROM DISK = N'C:\backup\sat_mig_runbook_2019.trn' WITH RECOVERY; |
While the database sat in RESTORING, it still reported internal version 904. That matches Microsoft’s log shipping upgrade article, which says a secondary database isn’t upgraded until it’s brought online.[4] The upgrade ran when the log restore brought it online:
|
1 2 3 4 5 6 7 8 |
Processed 0 pages for database 'sat_mig_runbook_up', file 'sat_mig_runbook' on file 1. Processed 4 pages for database 'sat_mig_runbook_up', file 'sat_mig_runbook_log' on file 1. Converting database 'sat_mig_runbook_up' from version 904 to the current version 998. Database 'sat_mig_runbook_up' running the upgrade step from version 904 to version 905. Database 'sat_mig_runbook_up' running the upgrade step from version 905 to version 906. [... 87 more upgrade-step lines removed for this post ...] Database 'sat_mig_runbook_up' running the upgrade step from version 997 to version 998. RESTORE LOG successfully processed 4 pages in 0.002 seconds (13.671 MB/sec). |
Afterward the database was ONLINE at internal version 998, with both rows present. Its compatibility level stayed at 130. Microsoft documents that a restored or attached database keeps its compatibility level if the new instance supports it.[1]
So the 2025 copy can’t go back to 2019 as a backup, and the only restore point is the 2019 database or a backup taken there. Anything written on 2025 after cutover isn’t in that copy. If you need those rows after a rollback, you have to plan how to carry them back.
Detach and Attach Go One Way Too
I created another scratch database on 2025 and detached it. I copied its files to the 2019 data folder, then attached the original files back on 2025. Attaching the copies on 2019 failed:
|
1 2 3 4 |
Msg 1813, Level 16, State 2, Server SQLWS01\SQL2019, Line 3 Could not open new database 'sat_mig_attach'. CREATE DATABASE is aborted. Msg 948, Level 20, State 1, Server SQLWS01\SQL2019, Line 3 The database 'sat_mig_attach' cannot be opened because it is version 998. This server supports version 904 and earlier. A downgrade path is not supported. |
Error 948 is severity 20, and it closed my connection. Microsoft lists severities 20 to 24 as fatal errors and says the application’s connection might also end.[5] Microsoft’s detach and attach article says an attached database is upgraded when it’s attached, which prevents its use with an older version of the Database Engine.[6] If you plan to move files rather than backups, keep a copy of the 2019 files that was never attached anywhere newer.
Compatibility Level Is the Part You Can Reverse
On the upgraded copy, I raised the compatibility level, lowered it part way, and then set it back to 130, where it started:
|
1 2 3 4 5 |
USE [master]; ALTER DATABASE [sat_mig_runbook_up] SET COMPATIBILITY_LEVEL = 170; ALTER DATABASE [sat_mig_runbook_up] SET COMPATIBILITY_LEVEL = 150; ALTER DATABASE [sat_mig_runbook_up] SET COMPATIBILITY_LEVEL = 130; |
| Step | Compatibility level | Internal version |
|---|---|---|
| After restore on 2025 | 130 | 998 |
After SET COMPATIBILITY_LEVEL = 170 |
170 | 998 |
After SET COMPATIBILITY_LEVEL = 150 |
150 | 998 |
After SET COMPATIBILITY_LEVEL = 130 |
130 | 998 |
All three changes ran without error, and the two rows were still there at the end. SQL Server 2025 accepts levels 100 through 170.[1] The internal version stayed at 998 the whole time. Lowering the level can undo a plan regression caused by the new level. It doesn’t make the database restorable on 2019. The step is also only mostly reversible. Some enhancements are available only at a higher level,[1] so code written after the change that needs the new level may stop working when the level goes back down. The same article notes that changing the level invalidates the plan cache for that database, so expect recompiles after each change.[1] Upgrading Database Compatibility Levels Across a Whole Instance covers when and how to raise the level.
Backing Out Before Cutover
At cutover, a tail-log backup WITH NORECOVERY captures the last log records and leaves the source in RESTORING, so nothing else can write to it.[7] I tested the rollback for that step on 2019. I inserted a third row, took the tail-log backup, and then brought the source back:
|
1 2 3 4 5 6 7 |
USE [master]; BACKUP LOG [sat_mig_runbook] TO DISK = N'C:\backup\sat_mig_runbook_2019_tail.trn' WITH INIT, CHECKSUM, NORECOVERY; RESTORE DATABASE [sat_mig_runbook] WITH RECOVERY; |
The database went from RESTORING back to ONLINE, with all three rows. Until applications write to the new server, this is the whole rollback: bring the source online and point the applications back at it.
Keeping a Path Back After Cutover
I didn’t test any of these, so the following comes from Microsoft Learn.
- Log shipping can’t go from a newer version to an older one. The log shipping upgrade article says a backup made on a newer version can’t be restored on an older one, which is why secondaries must be upgraded before the primary.[4]
- Availability groups behave the same way. An upgraded primary can’t ship logs to a secondary that’s still on the older version.[8] After you fail over to a 2025 replica, the 2019 replicas stop receiving changes.
- Transactional replication can run from new to old. The compatibility matrix lists SQL Server 2019 as a supported subscriber for a SQL Server 2025 publisher, and the distributor has to be on 2025 as well.[9] It’s a separate system to set up, monitor and test before cutover.
- Exporting changed rows from the new server and loading them into the old one is possible if the application tracks change dates. That’s application work, and it needs a test before cutover.
- Accepting the loss is the last option. Sometimes the answer is “we accept the loss.” If so, I want that signed before the window opens, along with how long the 2019 copy stays available.
The Runbook
This is the order I’d follow. Every step above the marked line can be undone by bringing the 2019 database back online.
- Run the compatibility and deprecated feature checks from the planning post. Record each database’s current compatibility level.
- Build the 2025 instance. Migrate logins with their SIDs, plus Agent jobs, linked servers and server settings.
- Take a full backup on 2019
WITH CHECKSUM. Restore it on 2019 under a different name to prove it restores. This is the rollback copy. - Seed 2025 by restoring that full backup
WITH NORECOVERY, and keep applying log backups from 2019. - Rehearse: restore a copy
WITH RECOVERYon a test instance, run the validation scripts, and time the upgrade steps. - Stop application writes on 2019. Take the tail-log backup
WITH NORECOVERY. - Restore the tail log on 2025
WITH RECOVERY. The database upgrades here. The 2019 source is still intact inRESTORING. - Leave the compatibility level where it was. Run the post-migration validation against 2025 before you open it to applications.
- Decide go or no-go. For no-go, run
RESTORE DATABASE ... WITH RECOVERYon 2019 and point the applications back.
Point of no return: the applications start writing to SQL Server 2025.
After that line:
- Rolling back to 2019 means losing every write made on 2025, unless you set up one of the options above. Leave the 2019 database in
RESTORING, and keep its final backups, for the agreed period. - Raise the compatibility level later, as its own change. If plans regress, set the level back. That step is mostly reversible on 2025, but code written to need the new level may not work after the level goes back down.
Limits of This Test
I tested one build of each version, on one workstation, with tiny databases. The upgrade on a large database may take longer, and a database from an older version may run more upgrade steps. I didn’t test log shipping, availability groups, replication or Managed Instance. The section on those relies on Microsoft Learn. I also didn’t test databases that use TDE, FILESTREAM or In-Memory OLTP.
Related: Azure SQL Managed Instance Differences That Catch DBAs During a Migration.
If your runbook has a rollback step I haven’t covered here, or you’ve used replication back to the old version after an upgrade, I’d like to hear how it went. Find me on Bluesky or LinkedIn.
References
- ALTER DATABASE Compatibility Level (Transact-SQL) - Microsoft Learn. Lists the supported levels per version, states that new databases take the lower of the default and the model level, that restored or attached databases keep a supported level, and that changing the level invalidates the plan cache. ↩
- DATABASEPROPERTYEX (Transact-SQL) - Microsoft Learn. Describes the Version property as an internal version number, informational only and not supported. ↩
- Copy Databases with Backup and Restore - Microsoft Learn. States that SQL Server backups can’t be restored by any earlier version of SQL Server. ↩
- Upgrade SQL Server Log Shipping - Microsoft Learn. Says to upgrade secondaries before the primary, because a backup created on a newer version can’t be restored on an older version. ↩
- Database Engine Error Severities - Microsoft Learn. Describes severities 20 to 24 as fatal errors and says the application connection to the Database Engine might also terminate. ↩
- Upgrade a database using detach and attach (Transact-SQL) - Microsoft Learn. States that an attached database is upgraded automatically, which prevents its use with an older version of the Database Engine. ↩
- Tail-log backups (SQL Server) - Microsoft Learn. States that a tail-log backup with NORECOVERY takes the database into the restoring state, which guarantees that the database doesn’t change after the backup. ↩
- Upgrade Availability Group Replicas - Microsoft Learn. States that an upgraded primary replica can no longer ship logs to a secondary replica that hasn’t been upgraded to the same version. ↩
- Replication backward compatibility - Microsoft Learn. The transactional replication matrix, which lists SQL Server 2019 as a supported subscriber for a SQL Server 2025 publisher and SQL Server 2025 as the only supported distributor for that publisher. ↩