none
TDE - corrupt backups when using backup database with compression, maxtransfersize

    Question

  • Out of 180 TDE encrypted databases on a server, 11 produce unusable backups when specifying compression and overriding maxtransfersize to enable TDE-optimized backup compression as outlined here - https://blogs.msdn.microsoft.com/sqlcat/2016/06/20/sqlsweet16-episode-1-backup-compression-for-tde-enabled-databases/.

    these 11 databases were not on the server when TDE was implemented, they were restored as copies of other databases that were.  Could this be a TDE issue related to restore database that we are seeing manifested downstream as a problem with backup database?

    dbcc checkdb() with no_infomsge, all_errormsgs, data_purity runs clean on these databases.

    Here are our symptoms...

    running restore verifyonly for these 11 database backups produces the following:

    Msg 3189, Level 16, State 1, Line 6
    Damage to the backup set was detected.
    Msg 3013, Level 16, State 1, Line 6
    VERIFY DATABASE is terminating abnormally.

    running restore database for these 11 database backups produces the following:

    Msg 3183, Level 16, State 1, Line 7
    RESTORE detected an error on page (1:197809) in database "test" as read from the backup set.
    Msg 3013, Level 16, State 1, Line 7
    RESTORE DATABASE is terminating abnormally.

    backing up the database with compression but without overriding maxtransfersize produces a valid backup for these databases but, of course, we lose TDE-optimized compression.  Restoring the valid non-compressed backup to a different server (SQL 2016 or SQL 2017), then backing it up again with both compression and maxtransfersize produces unusable backups just like on the original server, so it's something specific to the databases.

    If I remove/reapply TDE on these databases, then they produce valid backups when overriding maxtransfersize.  Unfortunately, all databases on the server produced good backups, then the 11 problematic ones appeared later, so I don't want to rely on decrypting/re-encrypting as a long-term solution as backup database/restore a copy events are common in our environment

    Sample backup command which produces valid backups on "most" databases but not the 11:

    backup database test
    to disk = '\\backupshare\share\test.bak'
    with checksum, compression ,maxtransfersize = 131072;

    Sample backup command which produces valid backups on all databases but without TDE-optimized compression:

    backup database test
    to disk = '\\backupshare\share\test.bak'
    with checksum, compression;

    Decrypt/encrypt "fixes" the probem:

    use test;
    alter database test set encryption off;
    alter database test set encryption on;

    SQL Versions:

    Microsoft SQL Server 2016 (SP1-CU6) (KB4037354) - 13.0.4457.0 (X64)   Nov  8 2017 17:32:23   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

    Microsoft SQL Server 2017 (RTM-CU3-GDR) (KB4052987) - 14.0.3015.40 (X64)   Dec 22 2017 16:13:22   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)



    Friday, February 02, 2018 6:03 PM

All replies

  • Hi KenJohnson,

    According to your description, my understanding is that this problem only occurs when both meeting following conditions:

    1. Back up from different machine and restore it.

    2. Backup Compression with TDE-enabled database.

    What about the version of the original instance and target instance? From 2017 to 2017 or 2016 to 2017? I will try to reproduce this problem.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 05, 2018 6:40 AM
  • Teige,

    Let me confirm condition 1.  Some of the backups/restores may be taking place on the same instance rather than across instances.

    [edit 2018-02-06]

    For condition 1, we have some backups from a different machine and some from the same that exhibit this behavior.

    Original and target instances are both 2016.  We don't have the 2017 servers available for the backup/restore workflow but i was able to take a non-compressed backup of one of these databases from 2016, restore it to 2017, then recreate the corrupt compressed backup from the 2017 server.  I just wanted to be sure that the problem followed the database to different instances and versions

    [/edit]

    Thanks,

    Ken


    • Edited by KenJohnson Tuesday, February 06, 2018 7:12 PM added clarification
    Tuesday, February 06, 2018 4:40 AM
  • Hi KenJohnson,

    Although I cannot reproduce this problem, but based on my searching, some other people had met a similar problem on SQL Server 2016.

    Firstly, I suggest you upgrading SQL Server 2016 to the latest version, if this cannot work, please open a connect at https://feedback.azure.com/forums/908035-sql-server

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 08, 2018 8:22 AM
  • Thanks.  Applied latest SQL updates.  No change, so posted to the new connect
    Thursday, March 15, 2018 7:59 PM