Post reply

SQL Backup Not Compressing with TDE Enabled

  • December 17, 2020 at 7:26 pm

    #3821726

    All,

    I ran into a strange bug and wanted to see if anyone else has experienced this. It's on a SQL 2019 Standard (CU 8) instance with a TDE enabled database and database compression turned on at the instance level.

    There seems to be a bug when backing up a TDE enabled database with backup compression enabled. Creating a backup to a new backup file will result in the .bak file being compressed. If you then take another backup with the same file name, overwriting the previous backup file, it will not be compressed.

    There was a documented bug where taking a backup of a TDE database with backup compression enabled (at either the instance level or with the COMPRESSION parameter) would result in the backup not being compressed. If the MAXTRANSFERSIZE = 131072 is provided (any value > 64K default) then the backup file would be compressed. This has been resolved in SQL 2019 CU5. But it seems that the bug still manifests itself when creating a backup that overwrites a previous backup file.

    In this scenario providing MAXTRANSFERSIZE = 131072 parameter will result in the backup being compressed even if it overwrites an existing compreseed backup file.

    support.microsoft.com/en-us/help/4561915/kb4561915-improvement-maxtransfersize-no-longer-required-to-enable-bac

    If I run this the first time the backup file will be compressed. If I run it again, the resulting file will not be compressed.

    BACKUP DATABASE MTOPIUAT TO DISK = 'G:\MTOPIUAT\MTOPIUAT.Bak' WITH COPY_ONLY, COMPRESSION, INIT;

    If I include the MAXTRANSFERSIZE parameter the file will always be compressed.

    BACKUP DATABASE MTOPIUAT TO DISK = 'G:\MTOPIUAT\MTOPIUAT.Bak' WITH COPY_ONLY, COMPRESSION, MAXTRANSFERSIZE = 131072,INIT;

    The knowledge base article said that the MAXTRANSFERSIZE parameter is no longer required, but that doesn't seem to be the case when backing up and overwriting an existing file.

    Has anyone experienced this strange bug?

    Thanks,

    Peter

     

     

    • This topic was modified 3 years, 11 months ago by  AZ Pete.
    • This topic was modified 3 years, 11 months ago by  AZ Pete.

  • December 17, 2020 at 7:44 pm

    #3821735

    I would reach out to Microsoft on this one as it sounds like a bug that they will need to fix.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply