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 |

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.

StackExchange Database Administrators Q#158632, answer score: 6