Unable to restore TDE enabled database when MAXTRANSFERSIZE and CHECKSUM is used 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: )
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.