Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

TDE - corrupt backups when using backup compression

After restoring a valid compressed backup of a TDE database to the same, or different, SQL server, backups of the newly restored database may be corrupt.

This is an intermittent problem. Out of dozens of restores, 0 or more of the restored databases may no longer be able to create valid backups when using backup compression.

If a restored database is affected by this problem, all subsequent compressed backups of the restored database are also corrupt until TDE is removed and reapplied to the database.

For me, this problem only applies to databases restored from compressed TDE backups. Compressed backups and restores of "original" databases (those to which TDE was originally applied or subsequently removed/reapplied) have always run clean.

Originally posted on social.msdn.com, I have since updated to SQL 2016 CU7 as recommended with no change in behavior (https://social.msdn.microsoft.com/Forums/office/en-US/dad3dfe3-c914-44f8-96b9-ab56cc825fe9/tde-corrupt-backups-when-using-backup-database-with-compression-maxtransfersize?referrer=http://social.msdn.microsoft.com/Forums/office/en-US/dad3dfe3-c914-44f8-96b9-ab56cc825fe9/tde-corrupt-backups-when-using-backup-database-with-compression-maxtransfersize?forum=sqldatabaseengine)

Independently reproduced by others (https://matthewmcgiffen.com/2018/02/16/tde-and-backup-compression-still-not-working)

BACKUP DATABASE [XXXXX] TO DISK = XXXXX’ WITH CHECKSUM, COMPRESSION, MAXTRANSFERSIZE = 131072

RESTORE VERIFYONLY FROM DISK = ‘XXXXX’ WITH CHECKSUM

SQL Version
Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64) Dec 22 2017 11:25:00 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

8 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Ken Johnson shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base