Problem

I had databases on a SQL Server 2008R2 instance using TDE (Transparent Data Encryption). Even though backup compression was enabled, the backups did not compress. I had googled this problem a few years ago and found that backups don’t compress in 2008R2 when TDE is enabled, and that it should work once I upgraded to SQL Server 2016 or later.

Recently, a SQL Server 2017 instance was deployed and I began moving databases for testing. Even though backup compression is the default setting at the instance level and in addition I also tried explicitly setting backup compression when taking a backup, I was still getting uncompressed backups.

Solution

It sounds like SQL Server 2019 CU5 contains a fix for it, but until then you can specify the MAXTRANSFERSIZE parameter to take a compressed backup of a TDE enabled database. For a TDE database, we need to use a MAXTRANSFERSIZE greather than 65536 (64 KB).

Here is a sample backup statement including MAXTRANSFERSIZE parameter as 128 KB (128KB * 1024 = 131072). Ideally, determine your optimum MAXTRANSFERSIZE through testing in your environment.

1
2
3

BACKUP DATABASE MyDatabase
TO DISK = N'd:\backup\MyDatabase.bak'
WITH COMPRESSION, MAXTRANSFERSIZE = 131072