SQL Server 2016 database backup fails to restore on SQL Server 2019

Question

We were trying to restore the database from SQL Server 2016 Developer Edition to SQL Server 2019 Developer Edition and it was failing with an exception.

Details about this DB:

1. It has in-memory filegroups
2. It was TDE enabled a year back, but that was removed since then.

Observations:

1. Was able to successfully restore and recover the same backup on SQL Server 2016 instance.
2. DBCC CHECKDB came back clean.
3. encryption_state of this database is set to 0.

Error from restore command on SQL 2019 instance:

Msg 41316, Level 16, State 0, Line 0
Restore operation failed for database 'database_01' with internal error code '0x82000000'.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Errors for error log on SQL 2019 instance:

LogDate             Source Text
-------             ------ ----
6/2/2021 4:16:08 PM spid55 Starting up database 'database_01'.
6/2/2021 4:16:08 PM spid55 [INFO] HkHostDbCtxt::Initialize(): Database ID: [5] 'database_01'. XTP Engine version is 2.11.
6/2/2021 4:16:08 PM spid55 The database 'database_01' is marked RESTORING and is in a state that does not allow recovery to be run.
6/2/2021 4:16:08 PM spid55 Error: 33126, Severity: 16, State: 1.
6/2/2021 4:16:08 PM spid55 Database encryption key is corrupted and cannot be read.
6/2/2021 4:16:08 PM spid55 [ERROR] HkRtRestoreDatabase(): Recovery failed with error 0x82000000 on database 5. This error will be mapped to 'HK_E_RESTORE_ABORTED' (0x82000018). (sql\ntdbms\hekaton\runtime\src\hkruntime.cpp:5505)
6/2/2021 4:16:08 PM spid55 restoreHkDatabase: DbId 5, Msg 41316, Level 16, State 0, Restore operation failed for database 'database_01' with internal error code '0x82000000'.
6/2/2021 4:16:08 PM spid55 [ERROR] HkHostRecoverDatabaseHelper::ReportAndRaiseFailure(): Database ID: [5] 'database_01'. Failed to load XTP checkpoint. Error code: 0x82000018. (sql\ntdbms\hekaton\sqlhost\sqlmin\hkhostdb.cpp:5741)
6/2/2021 4:16:08 PM spid55 [INFO] Database ID: [5]. Cleaning up StorageArray. LastClosedCheckpointEndTs: '0'

Answer 1

• After contacting Microsoft we come to know that we have encountered a rare situation which was blocking us with the restore on SQL Server 2019.
• To resolve this we had to share our backups with Microsoft to fix and they had to disable an assertion check on it manually. (weird, but the only solution)
• Good news is the hotfix is going to roll out soon.

Version Details:

Source: Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64)

Target: Microsoft SQL Server 2019 (RTM-CU10) (KB5001090) - 15.0.4123.1 (X64)

Answer 2

I think you have to drop the database encryption key on the source database on SQL Server 2016.

USE [YourDatabase]
GO 
DROP DATABASE ENCRYPTION KEY

Then you have to take the backup again. Now your restore should work on SQL server 2019.

(PS: take a backup of your db before dropping the EK)