Contents
Why is BAK file larger after TDE encryption?
After enabling TDE Encryption my database backup file .bak is 3x larger than before. Is there a way to improve this? Is this due to compression not being as efficient on an encrypted file?
Can you take an uncompressed backup for a TDE database?
To take an uncompressed backup for the TDE database, we can specify the NO_COMPRESSION clause or modify the default backup configuration. AS you know, SQL Server stopped providing service packs from SQL Server 2017 onwards. It only provides cumulative updates (CU) to fix the issues.
What happens when you enable transparent data encryption ( TDE )?
After you enable TDE, immediately back up the certificate and its associated private key. If the certificate becomes unavailable, or if you restore or attach the database on another server, you need backups of the certificate and private key. Otherwise, you can’t open the database.
Do you have to decrypt SQL database to use transparent data encryption?
To move a TDE database on SQL Database, you do not have to decrypt the database for the move operation. For more information on utilizing TDE with SQL Database, see Transparent Data Encryption with Azure SQL Database. After it is secured, the database can be restored by using the correct certificate.
How does SQL Server compression affect the backup size?
As illustrated in Figure 1, in SQL Server 2014 (and prior versions) backup compression doesn’t help reduce the backup size of a TDE-enabled database. However, due to the time spent in attempting the compression, the backup takes much longer to complete. Things change completely in SQL Server 2016.
How big is a BAK file when restored?
I got a 18.3 GB .bak file, when restored, the database size is about 78 MB, including logs (54MB). I understand that the opposite (backup smaller than restored database) might occur if there is unused space that was allocated in the source database, but this is not the case here.
Why does SQL Server 2016 backup take so long?
However, due to the time spent in attempting the compression, the backup takes much longer to complete. Things change completely in SQL Server 2016. You not only get great compression of the backup file size, but the backup time is reduced significantly as well, illustrated in Figure 2.