Until SQL 2016 if you used TDE (Transparent Data Encryption) you couldn’t use backup compression.
In 2016 Microsoft changed this, but it has been a rocky road. Backups work okay but in some circumstances people have found that they are corrupt when they come to restore them.
We thought that was all fixed, as highlighted by Brent Ozar, MS have been recommending that you are on at least SQL Server 2016 RTM CU6 or 2016 SP1 CU4 where these issues are resolved.
It seems like people are still having problems though. Ken Johnson is on 2106 SP1 CU6 and is having problems:
Our production DBA has just run a test and confirmed that in one test backing up 20 databases from one server and restoring them to another, about 10 have failed. Although in other environments this is working fine.
You can see the error if you try to verify the backup:
Date and time: 2018-02-16 09:52:10
Command: BACKUP DATABASE [XXXXX] TO DISK = XXXXX’ WITH CHECKSUM, COMPRESSION, MAXTRANSFERSIZE = 131072
Processed 95488 pages for database ‘XXXXX’, file ‘XXXXX’ on file 1.
Processed 3 pages for database ‘XXXXX’, file ‘XXXXX’ on file 1.
BACKUP DATABASE successfully processed 95491 pages in 2.375 seconds (314.113 MB/sec).
Date and time: 2018-02-16 09:52:12
Date and time: 2018-02-16 09:52:12
Command: RESTORE VERIFYONLY FROM DISK = ‘XXXXX’ WITH CHECKSUM
Msg 3189, Level 16, State 1, Line 1
Damage to the backup set was detected.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
Date and time: 2018-02-16 09:52:15
And it certainly doesn’t then work if you try to restore it:
5 percent processed.
Msg 3183, Level 16, State 1, Line 2
RESTORE detected an error on page (1:7763) in database “XXXXXXX as read from the backup set.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
We’re raising a call with Microsoft, will update when we know more.
In the meantime, can we trust backups of TDE with backup compression enabled? Given that we want to be absolutely sure our backups can save us in event of disaster the answer may be no. At a minimum we need to test each time we update our applications or patch SQL Server.
The side lesson if course is that we should always be regularly checking our backups are restorable.
More articles on TDE
What is Transparent Data Encryption?
Setting up Transparent Data Encryption (TDE)
Encrypting an existing database with TDE
Understanding Keys and Certificates with Transparent Data Encryption (TDE)
How Secure is Transparent Data Encryption (TDE) – and How to Prevent Hacking
Migrating or Recovering a TDE protected Database
If this post has helped you, consider buying me a coffee to say thanks.
7 thoughts on “TDE and backup compression – still not working?”
++ 1000 On this. I rushed to change our backups for our SQL 2016 to put in the maxtransfersize to be able to get compression back. Fail…
The backup would go and be smaller in size. But the restore took 10x as long as normal and then failed 90% of the time.
So I backed out the changes and just had to live with the non-compressed backups.
Matthew, I’m glad you could reproduce this and make it more publicly available. I’ve also submitted this to the new “connect” – https://feedback.azure.com/forums/908035-sql-server/suggestions/33482023-tde-corrupt-backups-when-using-backup-compressio
Thanks for the feedback, the intemittent nature of the problem is the same for us. We’re trying to capture a database that is in this state so we can supply to MS. I’ve been impressed with their response on this issue, obviously the unreliability in repeating it creates challenges to investigation and fixing.
haven’t yet validated but this is reportedly fixed in SQL 2016 SP1-CU2 / SQL 2017 CU7 – https://support.microsoft.com/en-us/help/4101502/tde-enabled-database-backup-with-compression-causes-corruption
I am still getting error SQL 2016 SP2-CU3 and the intermittent of the problem is the same for me. Only thing I’ve noticed is it only happen with VLDB (>1TB).