TDE - corrupt backups when using backup compression
After restoring a valid compressed backup of a TDE database to the same, or different, SQL server, backups of the newly restored database may be corrupt.
This is an intermittent problem. Out of dozens of restores, 0 or more of the restored databases may no longer be able to create valid backups when using backup compression.
If a restored database is affected by this problem, all subsequent compressed backups of the restored database are also corrupt until TDE is removed and reapplied to the database.
For me, this problem only applies to databases restored from compressed TDE backups. Compressed backups and restores of "original" databases (those to which TDE was originally applied or subsequently removed/reapplied) have always run clean.
Originally posted on social.msdn.com, I have since updated to SQL 2016 CU7 as recommended with no change in behavior (https://social.msdn.microsoft.com/Forums/office/en-US/dad3dfe3-c914-44f8-96b9-ab56cc825fe9/tde-corrupt-backups-when-using-backup-database-with-compression-maxtransfersize?referrer=http://social.msdn.microsoft.com/Forums/office/en-US/dad3dfe3-c914-44f8-96b9-ab56cc825fe9/tde-corrupt-backups-when-using-backup-database-with-compression-maxtransfersize?forum=sqldatabaseengine)
Independently reproduced by others (https://matthewmcgiffen.com/2018/02/16/tde-and-backup-compression-still-not-working)
BACKUP DATABASE [XXXXX] TO DISK = XXXXX’ WITH CHECKSUM, COMPRESSION, MAXTRANSFERSIZE = 131072
RESTORE VERIFYONLY FROM DISK = ‘XXXXX’ WITH CHECKSUM
SQL Version
Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64) Dec 22 2017 11:25:00 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
Ken Johnson
shared this idea