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, Level 16, State 1, Line 1 --VERIFY DATABASE is terminating abnormally. RESTORE DATABASE [TDE_DB2] FROM DISK = 'E:\MSSQL\Backup\TDE_DB1b.bak' WITH MOVE 'DataFileName' to 'E:\MSSQL\Data\TDE_DB2.mdf' ,MOVE 'LogFileName' to 'F:\MSSQL\Log\TDE_DB2_log.ldf'; -- ERROR --Msg 3183, Level 16, State 1, Line 7 --RESTORE detected an error on page (1:92454) in database "TDE_DB2" as read from the backup set. --Msg 3013, Level 16, State 1, Line 7 --RESTORE DATABASE is terminating abnormally.
I then tried to check the page that is reported as having the error (It is always the same page.), but DBCC PAGE reports that the ObjectId is 0. According to this article by Paul Randal that means there was no metadata found, and one of the reasons could be that the page itself is corrupt and incorrect values were used to try to look up the metadata. His advice is to run CHECKDB, which I cannot do because the corrupted backup won’t restore.
I tried the suggestions from this SO Post (Adding INIT and FORMAT to the BACKUP command) to reset the metadata, but that did not seem to change anything, I still get corruption on the compressed backup.
This only happens with one of my TDE database. I have 4 other TDE databases on this same server, and they do not have this problem. That tells me that there may be an underlying problem with this specific database. I realize that the easy solution is to just not use compression, but I feel like this may actually be an early warning to a bigger problem coming down the road.
Has anyone ever seen this before, or have any idea why compression would corrupt that page? At this point, I’m kind of at a loss as to what to do next. I considered restoring the page from an earlier backup, but I don’t think that would matter since the page in the regular database seems fine.
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!