question

lperumal avatar image
lperumal asked

I have SQL 2014 installed on a server and have TDE enabled on the databases. Is there an option to compress the backup of the TDE enabled databases?

The size of the database is 4 Tb and the backup size seems to be almost the same without any compression. So looking for options to compress the backup
backup-compression
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
Sorry, it looks like TDE prevents effective backup compression: > Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended. This is from Books Online: https://msdn.microsoft.com/en-gb/library/bb934049(v=sql.120).aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnSterrett avatar image
JohnSterrett answered
You might want to also try some 3rd Party backup vendor like SQL Backup Pro by RedGate. They provide different compression settings so you might see some improvement compared to default compression in SQL Server but generally compression looks for patterns to compress which you wouldn't see with TDE backups compared to regular backups.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.