backup header "incomplete" when backup compression is used on TDE-enabled VLDB database
After applying SP2 and CU3 for SQL Server 2016 I get message "incomplete" when I read a backup header for a VLDB that has TDE enable.
My DB has 1.2 TB with FileStream enable, Page Compression is being used for some table and AG with 2 nodes.
I am following some recommendation such as this: https://blogs.msdn.microsoft.com/sqlserverteam/backup-compression-for-tde-enabled-databases-important-fixes-in-sql-2016-sp1-cu4-and-sql-2016-rtm-cu7/
We faced with some problems that are documented in these KB:
When I run the RESTORE HEADERONLY FROM DISK command I got this result
For this backup I use these following
BACKUP DATABASE mydatabase
TO DISK = 'pathtodisk'
(ALGORITHM = AES_256, SERVER CERTIFICATE = [BackupCert])
,MAXTRANSFERSIZE = 1048576;
Because of this I cannot use this backup. However when I remove MAXTRANSFERSIZE, my backup is not compressed (as expected) but at least I can use it and backup header shows information about it.