Resolving snapshot issues using SQL Server compressed and encrypted backups (KBA9259)

Issue

When performing a SQL Server snapshot of a database using Transparent Data Encryption (TDE) using SQL Server 2019, the snapshot may fail with the following error:

Error: For dSource "TDE_DB", failed to restore a source database backup.

Error Code: exception.db.mssqlstagingdb.stagingdb.backup.restore.failed

Suggested Action: Make sure that the SQL Server instance "MSSQLSERVER" on the staging host "myserver.mydomain" is up, the user "MYDOMAIN\delphix_os" has privileges to restore a database on the host, the host has enough resources, and then try the operation again.

The listed Command output may include either of the following:

Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Msg 3241, Level 16, State 40, Line 1
The media family on device '\\MYSERVER\Backups\TDE_DB.bak' is incorrectly formed. SQL Server cannot process this media family.

Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Msg 9004, Level 21, State 1, Line 1
An error occurred while processing the log for database 'TDE_DB'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Major Release	All Sub Releases
6.0	6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1, 6.0.13.0, 6.0.13.1, 6.0.14.0

Resolution

This issue can be resolved in one of the following ways:

• Ensure that both the Source and Target are running SQL Server 2019 CU16 or newer, and take a new backup; or
• Take a new backup that does not use backup compression

The underlying cause of this issue is Microsoft bug KB5014298 - FIX:  Error 3241 occurs during executing RESTORE DATABASE OR RESTORE LOG.

Issues relating to backup compression of encrypted databases were resolved in SQL Server 2019 CU16 (KB5011644). The fix for this issue results in backups from SQL Server 2019 CU16 being unreadable by CU15 or earlier.

It is important to note that both the Source and Staging environments must be patched for this issue and a new backup taken, before this issue can be fully resolved.