Unable to restore TDE enabled database when MAXTRANSFERSIZE and CHECKSUM is used

Question

Update: @AmitBanerjee - Senior Program Manager for the Microsoft SQL Server Product Group confirmed that MS will look into the issue as it is a defect.

Has anyone encountered issue restoring backups taken on SQL Server 2016 with TDE enabled and using MAXTRANSFERSIZE > 65536 (in my case, I have chosen 65537 so that I can compress TDE database) and CHECKSUM ?

Below is a repro:

--- create database 
create database test_restore
go
-- create table
create table test_kin (fname char(10))
go
-- Enable TDE 

use master
GO
CREATE CERTIFICATE test_restore WITH SUBJECT = 'test_restore_cert'
GO
SELECT name, pvt_key_encryption_type_desc, * FROM sys.certificates WHERE name = 'test_restore'
GO
use test_restore
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE test_restore
GO 
alter database test_restore set encryption ON

Take full copy only backup .. do it twice ..

backup database test_restore 
to disk = 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak' -- change as per your location !!
with init, stats =10  -- overwrite ..using INIT !!
, maxtransfersize = 65537
, compression
,CHECKSUM

Now do a verifyonly ...

restore verifyonly from disk = 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak'

Error Message :

Msg 3241, Level 16, State 40, Line 11 The media family on device 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak' is incorrectly formed. SQL Server cannot process this media family. Msg 3013, Level 16, State 1, Line 11 VERIFY DATABASE is terminating abnormally.

Results (1 = ON, 0 = OFF) with different combinations :

+-------------------------+-------------+----------+--------+
| MAXTRANSFERSIZE (65537) | COMPRESSION | CHECKSUM | RESULT |
+-------------------------+-------------+----------+--------+
|                       1 |           1 |        1 | FAIL   |
|                       1 |           1 |        0 | PASS   |
|                       1 |           0 |        1 | FAIL   |
|                       0 |           0 |        0 | PASS   |
|                       0 |           1 |        1 | PASS   |
|                       0 |           1 |        0 | PASS   |
+-------------------------+-------------+----------+--------+

The issue happens on:

Microsoft SQL Server 2016 (RTM-CU1) (KB3164674) - 13.0.2149.0 (X64) Jul 11 2016 22:05:22 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )

Answer 1

I was able to reproduce your problem.

Adding FORMAT to the BACKUP command solved it for me.

While I can't seem to find concrete documentation, it's my opinion that this is related to the fact that INIT retains the existing media header on the backup set while FORMAT creates a new media header.

I'm still researching this issue and if I find additional information, I will update this answer.

Answer 2

Seems like this might have been addressed with KB 4032200:

• FIX: Fail to compress the backup file when INIT and COMPRESSION option is used in a TDE enabled database in SQL Server 2016

From that entry:

Symptoms

Assume that you enable Transparent Data Encryption (TDE) for a database in Microsoft SQL Server 2016. You try to backup the database by using the BACKUP DATABASE T-SQL statement that has both COMPRESSION and INIT option specified. In this scenario, you may notice that the existing backup file is overwritten by the new backup file, and the new backup file is not compressed.

Resolution

This issue is fixed in the following cumulative updates for SQL Server:

• Cumulative Update 7 for SQL Server 2016 RTM
• Cumulative Update 4 for SQL Server 2016 SP1

Answer 3

This would appear to potentially be the same issue that the blog post you referenced in your question was later updated to refer to:

Update April 6th, 2017

We have recently discovered some issues related to the use of TDE and backup compression in SQL Server 2016. While we fix them, here are some tips to help you avoid running into those known issues:

• Currently it is not advisable to use striped backups with TDE and backup compression

• If your database has virtual log files (VLFs) larger than 4GB then do not use backup compression with TDE for your log backups. If you don’t know what a VLF is, start here.

• Avoid using WITH INIT for now when working with TDE and backup compression. Instead, for now you can use WITH FORMAT.

SQL engineering is working on fixes for these issues in SQL Server 2016. We will update this blog post once again once we have further information to share.

Despite that note, the blog post has not been updated with any further information since then.

However, KB 4019893 may also address this:

• FIX: Restore fails when you do backup by using compression and checksum on a TDE enabled database in SQL Server 2016

Although the error message reported in that KB article is different than the one you are reporting, the contributing factors sound very similar. SQL Server 2016 SP1 CU3 first included the fix, as seen in its hotfix list. However, there have been reports that it did not resolve the issue in all situations.

SQL Server 2016 SP1 CU4 also includes a (presumably updated) fix for this, and KB 4019893 has since been updated to show SP1 CU4 as the version the issue was fixed in.

Unfortunately, I can confirm from my own experience that even the fix in SP1 CU4 does not fully resolve that issue. I currently have one TDE-enabled database that still produces consistently corrupt backups even on SP1 CU4 when using COMPRESSION (via MAXTRANSFERSIZE > 64 KB) and CHECKSUM. I also have several dozen other TDE-enabled databases in this environment that consistently don't produce corrupt backups under those settings, including one that is a variation of the one that does, with a nearly identical schema but smaller dataset. This would seem to indicate that Microsoft is indeed chipping away at the scenarios that can cause this, but has not resolved all of them yet.

I've not yet tried using FORMAT to work around this issue, as referenced in another answer and the SQLCAT blog post, but I'll provide an update here if I'm able to try that and it resolves the issue. The one database I have that reproduces this is unfortunately rather large (~1 TB), and resides in a Development / QA cluster that doesn't have much extra storage space available (at least at that scale), so testing variations of this has proven to be logistically challenging and time-consuming.