Backing up SQL 2016 TDE Database
rickmhall
Posts: 2
Does SQL Backup 8 support compressed backups when backing up SQL 2016 TDE databases? When I change the MAXTRANSFERSIZE to anything greater than 64K using Microsoft SQL Backup then I get about 78% compression of the TDE database. I am not seeing any compression from Redgate and have tried numerous MAXTRANSFERSIZEs.
Thank you,
Rick
Thank you,
Rick
Tagged:
Comments
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
Version 9.1.4 August 31st, 2017
This release of SQL Backup includes the following enhancements and bug fixes:
Fixes
The database is 30Gb with TDE applied to it.
Clearly SQL Backup can't be used with a TDE database if you want to also achieve compression on the backups.
They have fixed TDE to work using the SQLCOMPRESSION option and the Microsoft SQL Bug was fixed with a SP and CU some time ago. Please make sure you have a fairly recent SP/CU as you could have issues if you are using a version of SQL 2016 that doesn't have the fix, sorry I don't know the exact time it was fixed but you could look it up or just make sure your SQL Server patching is sufficiently up to date.
You also need to add an undocumented key to the registry: REG_DWORD to HKLM\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\(nameofinstance) called FORCETDECOMPRESSION with a value of 1. If you have further questions check with Red Gate support.
Thanks for that info. Do you manage to get as good (if not better) compression than using native SQL?
For our backup files, our full backups are being compressed, but our transaction log backups are not compressed. Is this expected?
Also, I can't find Redgate SQL Backup documentation with specific instructions on backing up and compressing TDE enabled databases. If there is one, please provide a link. If not, please ask your documentation department to write one. Thanks.
With the release of V10.1.14 back in November 2021, the previous requirement to add a registry key called ForceTDECompression is no longer required. Also a new exit code 463 was added to generate a warning when a TDE enabled database is not backup up using the SQLCOMPRESSION, to highlight to SQL Backup users that they should be using SQLCOMPRESSION and not SQL Backup's compression algorithms.
Sean Quigley | Product Support Engineer | Redgate Software
Have you visited our Help Center?
Sean Quigley | Product Support Engineer | Redgate Software
Have you visited our Help Center?