3

I receive a full backup of a very large database once per week from a vendor. It contains transactional data which we then load into our data warehouse.

Fully restored, the database is around 707GB. It is comprised of 3 files, 1 data file which is about 385GB, a second data file which is about 132GB, and 1 log file which is about 190GB.

This database has TDE (transparent data encryption) enabled. Everything that I've read says that with TDE, backup compression should be minimal, if any. However, the full backup file which I receive is 386GB in size. This is about 54% of the total file size before the backup.

I have been told that the vendor is performing native SQL backups and the file is not decrypted before they back it up.

How are the backup files so much smaller than the database files? I'm really hoping someone can help shed some light on this for me.

3

Community Wiki answer generated from comments on the question by QueueMann and Aaron Bertrand♦

Database backup files throws away any unused space in your data files in the backup process.

TDE doesn't have to encrypt empty pages, and the backup process doesn't have to include the space in the backup, but they are still empty pages contributing to data file size.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.