Query Store transaction preventing restoring database

This was so much to learn from. I really appreciate the efforts you two made here MikeRM2​ MW_DEV​ 

Hi. Very interesting case. I’m not entirely sure whether this will solve your problem, as I wasn’t able to reproduce this scenario, but these are the steps I would take. 

Based on the information you collected, I would approach this by fixing the Query Store state on SQL Server 2019 first, creating a completely new clean backup, and only then restoring that backup to SQL Server 2025. The fact that the same backup can be restored and brought online on SQL Server 2019, but SQL Server 2025 remains in recovery, strongly suggests that the problem occurs while SQL Server 2025 is performing recovery and database upgrade.

SQL Server recovery consists of three phases:

1. Analysis
2.  Redo
3. Undo

During the Undo phase, SQL Server rolls back transactions that were still active at the recovery point. 

Microsoft documents this process here:

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-and-recovery-overview-sql-server?view=sql-server-ver17  

In your case, sys.dm_tran_active_transactions shows those LSNs are essentially adjacent, so it looks like recovery is failing while trying to undo this particular transaction. Since your investigation points to Query Store, I would not continue trying to repair the copy that is already stuck on SQL Server 2025. Instead, I would use the working SQL Server 2019 copy to remove or repair the Query Store state before taking a new backup.

The sequence I would use is the following:

1.Restore the database on SQL Server 2019

Use the instance where you already know the database can successfully complete recovery and come online. Do not use the SQL Server 2025 copy for the repair process.

2. Run DBCC CHECKDB first

Before changing anything, verify that the source database is physically and logically consistent:

DBCC CHECKDB (N'BrokenDatabase') 
WITH NO_INFOMSGS, ALL_ERRORMSGS; 
GO

Microsoft recommends validating source databases with DBCC CHECKDB before migration:

https://learn.microsoft.com/en-us/ssms/migrate/upgrade-sql-server 

This step is especially important because the error log contains:

Page (...) is marked RestorePending, which may indicate disk corruption.

If CHECKDB reports allocation or consistency errors, I would stop here and investigate those first

Ideally the result should be 0 allocation errors and 0 consistency errors

3. Check the current Query Store state

SELECT 
actual_state_desc, 
desired_state_desc, 
current_storage_size_mb, 
max_storage_size_mb, 
readonly_reason, 
interval_length_minutes, 
stale_query_threshold_days, 
size_based_cleanup_mode_desc,
query_capture_mode_desc 
FROM sys.database_query_store_options;

Pay particular attention to: actual_state_desc, desired_state_desc and readonly_reason.

4. Turn Query Store completely OFF

I would not use READ_ONLY for this operation. Use:

ALTER DATABASE BrokenDatabase 
SET QUERY_STORE = OFF; 
GO

Then verify the state again. This distinction is important. READ_ONLY still leaves Query Store active. OFF disables Query Store, which is required before running the Query Store consistency repair procedure.

5. Run Query Store consistency repair

Starting with SQL Server 2017, Microsoft provides a specific procedure for recovering an inconsistent Query Store:

EXEC BrokenDatabase.dbo.sp_query_store_consistency_check; 
GO

Microsoft explicitly states that Query Store must be disabled before running this procedure. The documented recovery procedure is:

ALTER DATABASE [myDatabase] 
SET QUERY_STORE = OFF;

 EXECUTE [myDatabase].dbo.sp_query_store_consistency_check; 

ALTER DATABASE [myDatabase] 
SET QUERY_STORE = ON; 

ALTER DATABASE [myDatabase] 
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

For this migration, however, I would not enable Query Store again yet. I would leave it OFF until the database has successfully migrated to SQL Server 2025.

Documentation: 

https://learn.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver17#verify-that-query-store-collects-query-data-continuously 

So in your case:

ALTER DATABASE BrokenDatabase SET QUERY_STORE = OFF; 
GO

 EXEC BrokenDatabase.dbo.sp_query_store_consistency_check; 
GO

6. Optionally clear Query Store

ALTER DATABASE BrokenDatabase 
SET QUERY_STORE CLEAR; 
GO

Microsoft documents QUERY_STORE CLEAR as another recovery option when Query Store data is damaged. However, I would consider this secondary to the consistency check.

If I understood correctly, You previously attempted to clear Query Store while it was still active and encountered the ASYNC_LOAD wait. The important difference here is that Query Store is first disabled and its internal consistency is checked. If CLEAR again becomes stuck, I would not necessarily block the migration on it. I would leave Query Store OFF and continue with the remaining validation

7. Force a checkpoint - This gives SQL Server an opportunity to persist the clean database state

USE BrokenDatabase; 
GO 
CHECKPOINT; 
GO

8. Check the oldest active transaction again

DBCC OPENTRAN (BrokenDatabase); 
GO

This is a very useful validation step. I would specifically check whether that transaction is still present. If the transaction disappears after

QUERY_STORE = OFF 
sp_query_store_consistency_check
CHECKPOINT

that would be a strong indication that Query Store was involved in the recovery problem.

9. Run DBCC CHECKDB again before taking the new backup:

DBCC CHECKDB (N'BrokenDatabase') 
WITH 
  NO_INFOMSGS, 
  ALL_ERRORMSGS; 
GO

I would not migrate the database until CHECKDB completes cleanly.

10. Create a completely new full backup

Do not reuse the original backup. Create a new backup from the cleaned SQL Server 2019 database

BACKUP DATABASE BrokenDatabase 
TO DISK = N'X:\Backup\BrokenDatabase_Clean.bak' 
WITH 
 COPY_ONLY, 
 CHECKSUM,
 INIT, 
 STATS = 5; 
GO

WITH CHECKSUM is important because SQL Server validates page checksums while creating the backup and also writes backup checksums.

11. Verify the new backup

RESTORE VERIFYONLY 
FROM DISK = N'X:\Backup\BrokenDatabase_Clean.bak' 
WITH CHECKSUM; 
GO

Keep in mind that RESTORE VERIFYONLY does not replace DBCC CHECKDB

12. Make sure SQL Server 2025 is fully patched - always worth to check it

SELECT 
@@VERSION, 
SERVERPROPERTY('ProductVersion') AS ProductVersion, 
SERVERPROPERTY('ProductLevel') AS ProductLevel, 
SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel; 
GO

I would test this on the current SQL Server 2025 CU rather than RTM or an older build. I did not find a documented SQL Server 2025 fix that explicitly says something like "restore of SQL Server 2019 database hangs during Query Store undo", so I would not claim that a particular CU fixes this exact problem. However, reproducing a Database Engine recovery problem on the latest CU is important before escalating it to Microsoft

13. Finally - restore the new backup to SQL Server 2025

Restore the newly created backup, not the original one. The original backup already contains the transaction/log state and every time you restore that same old backup, SQL Server has to reconstruct that same historical recovery state and eventually process that same transaction. You cannot remove that transaction from an existing backup

RESTORE DATABASE BrokenDatabase 
FROM DISK = N'X:\Backup\BrokenDatabase_Clean.bak' 
WITH 
  MOVE N'<logical_data_file>' TO N'<new_data_path>', 
  MOVE N'<logical_log_file>' TO N'<new_log_path>', 
RECOVERY, 
STATS = 5; 
GO

SQL Server will perform both database recovery and the internal database-version upgrade.

I know,  It may look a bit overwhelming or overly cautious, but by restoring it successfully on SQL Server 2019, you have an opportunity to allow recovery to complete, repair/disable Query Store, checkpoint the database, verify consistency, and then create a new backup representing a new clean recovery point. There is no reason to perform a destructive repair while you still have a database that can successfully come online on SQL Server 2019. Likewise, switching the SQL Server 2025 copy between EMERGENCY, SINGLE_USER, and ONLINE does not fix the underlying Undo operation - at least I am not aware of it. 

If after disabling Query Store, running sp_query_store_consistency_check, executing CHECKPOINT, and creating a new backup, SQL Server 2025 still fails at exactly the same LSN or page, I would treat that as a strong candidate for a SQL Server engine issue rather than continuing to manipulate Query Store manually.

At that point I would reproduce the problem on the latest SQL Server 2025 CU and open a Microsoft Support case with:

• the SQL Server 2019 and 2025 exact builds,
• the ERRORLOG from both servers,
• DBCC CHECKDB results,
• DBCC OPENTRAN output,
• sys.dm_tran_active_transactions,
• Query Store state,
• the exact failing LSN,
• and, if possible, a reproducible backup.

I would definitely not modify sys.internal_tables or Query Store internal tables directly

Sorry again for the wall of text. I hope this line of thinking is clear and will at least help point you in the right direction toward a solution.