Backup Compression Causing Corruption In SQL 2017 TDE Database

On SQL Server 2017 (CU3), whenever I enable backup compression on one of my TDE databases, the backup process always corrupts a specific page in the database. If I run the backup without compression, it does not get corrupted. Here are the steps I've taken to verify and reproduce this issue:

• Run DBCC CheckDB on database "TDE_DB1"; all is good, no errors;

• Successfully back up database without compression; RESTORE VERIFYONLY says all is good;

• Successfully restore database as "TDE_DB2"; all is good, DBCC CheckDB shows no errors;

• Successfully back up "TDE_DB1" database WITH compression; RESTORE VERIFYONLY errors, saying "Damage to the backup set was detected";

• Attempt to restore database as "TDE_DB2"; errors, saying "RESTORE detected an error on page (1:92454) in database"

• Repeat steps 1-3; all is good;

• DROP "TDE_DB1" and "TDE_DB2"; Restore "TDE_DB1" from backup; all is good;

• Repeat steps 1-5; get same results;

To summarize: The database and regular backups seem fine, running CHECKDB on the database and VERIFYONLY on the backups do not report any errors. Backing up the database with compression seems to cause the corruption.

Below are the code samples with errors. (Note: MAXTRANSFERSIZE is required for using compression with a TDE database)

```
-- Good, completes with no corruption;
BACKUP DATABASE [TDE_DB1] TO DISK = N'E:\MSSQL\Backup\TDE_DB1a.bak' WITH CHECKSUM;
RESTORE VERIFYONLY FROM DISK = N'E:\MSSQL\Backup\TDE_DB1a.bak' WITH CHECKSUM;

RESTORE DATABASE [TDE_DB2]
FROM DISK = 'E:\MSSQL\Backup\TDE_DB1a.bak'
WITH MOVE 'DataFileName' to 'E:\MSSQL\Data\TDE_DB2.mdf'
,MOVE 'LogFileName' to 'F:\MSSQL\Log\TDE_DB2_log.ldf';

-- Bad, I haz corruption;
BACKUP DATABASE [TDE_DB1] TO DISK = N'E:\MSSQL\Backup\TDE_DB1b.bak' WITH CHECKSUM, COMPRESSION, MAXTRANSFERSIZE = 131072;
RESTORE VERIFYONLY FROM DISK = N'E:\MSSQL\Backup\TDE_DB1b.bak' WITH CHECKSUM;
-- ERROR
--Msg 3189, Level 16, State 1, Line 1
--Damage to the backup set was detected.
--Msg 3013,

It looks like this issue is with databases that have had SHRINK operations run on them. In my case, I was taking a copy of one of our production databases on SQL Server 2014 (which is already encrypted with TDE), running DBCC SHRINKFILE on both the data and log files, then taking a backup and restoring it on my new SQL 2017 Server. (The reason for the shrink was to reduces the size to make transferring the backup faster.)

As a test, I restored a copy of the database that I did not run DBCC SHRINKFILE on, and it did not have the corruption problems when compressing backups.

So, to summarize, the results of my testing are as follows:

• Normal backup/restore operations on this “shrunken” TDE database work

correctly in SQL 2017

• Compressing backups of the “shrunken” TDE

database seem to cause corruption in the sys.sysmultiobjrefs table

• Compressing backups of the regular TDE database (not having DBCC

SHRINKFILE run) work correctly and do not report corruption

I do not know if this is a confirmed bug in SQL Server 2017, but I have sent my findings to Microsoft for them to look over.

So, the moral of this story is: DO NOT SHRINK YOUR DATABASES! EVER! :)

StackExchange Database Administrators Q#195078, answer score: 8