Symptoms
In Microsoft SQL Server 2019, restoring the compressed database or log backups of the databases that have transparent data encryption (TDE) enabled may cause the following error:
Msg 3241, Level 16, State 18, Line <LineNumber>
The media family on device '<backup file name>' is incorrectly formed. SQL Server cannot process this media family.
Workaround
To work around this issue, do not compress the backups of TDE-enabled databases by using either of the following methods:
-
Use WITH COMPRESSION as described in BACKUP (Transact-SQL).
-
Rely on backup compression default as described in View or Configure the backup compression default Server Configuration Option.
Resolution
Cumulative update information
This problem is fixed in the following cumulative update for SQL Server:
Cumulative Update 16 for SQL Server 2019
Note You need to create the backups together with this fix to avoid the problem. Installing the fixed CU on the target instance and trying to restore the same backup created without the fix will not work.
More information
Important: Starting with SQL Server 2019 CU16, the creation of compressed backups (database or log) of TDE-enabled databases will use a new backup format that can only be restored on an instance that has CU16 or later installed.
Restoring a compressed backup of a TDE-enabled database that's created on CU16 or later on a SQL Server 2019 instance of version CU15 or earlier fails and causes the following errors:
-
RESTORE DATABASE
Msg 3013, Level 16, State 1, Line <LineNumber>
RESTORE DATABASE is terminating abnormally.
Msg 9004, Level 21, State 1, Line <LineNumber>
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.
-
RESTORE LOG
Location: mediaRead.cpp:1018
Expression: readSize <= m_Demand
SPID: 84
Process ID: ProcessID
Msg 3013, Level 16, State 1, Line <LineNumber>
RESTORE LOG is terminating abnormally.
Msg 3624, Level 20, State 1, Line <LineNumber>
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.
Note RESTORE HEADERONLY and RESTORE FILELISTONLY are unaffected by the issue and will work in all cases.
RESTORE VERIFYONLY may return successfully for a FULL backup that is invalid as per the above scenario: do not rely on RESTORE VERIFYONLY to establish that the backup can be restored without hitting the above issue. RESTORE VERIFYONLY against a log backup will usually fail together with the same error as an actual RESTORE LOG described above.
Therefore, it's important to make sure that in a context where TDE and Backup Compression may be enabled, any SQL Server 2019 instances consuming backups from other SQL Server 2019 instances receive CU16 (or later) before the instances that generate the backup material. Log shipping architectures would be a prime example of such a situation: upgrade secondary instances first.
Once a transaction log backup has been created with compression, it is usually not possible to recreate it without compression. Therefore, upgrading the Log Shipping primary server to SQL Server 2019 CU16 or later in such a context would break the restoring jobs until the secondary server is also upgraded.
An uncompressed backup of a TDE-enabled database, a compressed backup of a database that's not enabled for TDE, or an uncompressed backup of a database that's not enabled for TDE will not use the new backup format introduced in CU16, and can be restored on a SQL Server 2019 instance of any versions.
It is therefore required to disable backup compression if you plan to restore a TDE-enabled database material (either full backup or transaction log backup) to any SQL Server instances of earlier versions before SQL Server 2019 CU16.
Each new cumulative update for SQL Server contains all the hotfixes and security fixes that were in the previous build. We recommend that you install the latest build for your version of SQL Server:
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
References
Learn about the terminology that Microsoft uses to describe software updates.
