Backup detects corruption, but CHECKDB does not

I have a database where when I run the backup command

BACKUP DATABASE [MyDatabase] TO     
DISK =  'G:\Backup\MyDatabase_01_01_2018.bak'   
WITH    NOFORMAT, NOSKIP, COMPRESSION, INIT, BUFFERCOUNT = 100

I get the error message


Msg 3043, Level 16, State 1, Line 8

BACKUP 'MyDatabase' detected an error on page (1:745345) in file 'F:\Data\MyDatabase_1.ndf'.

Msg 3013, Level 16, State 1, Line 8

BACKUP DATABASE is terminating abnormally.

I ran a full CHECKDB but it comes back clean. I did notice that the Page Verify option had been set to NONE (not my doing) so I changed it to CHECKSUM and rebuilt all the indexes in the DB to get it to write to all pages and generate checksums. After this the backup still fails and the checkdb still shows clean (so no change).

DBCC CHECKDB('MyDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS,
DATA_PURITY, EXTENDED_LOGICAL_CHECKS;

from the SQL log:


DBCC CHECKDB (MyDatabase) WITH all_errormsgs, no_infomsgs, data_purity executed by xxx found 0 errors and repaired 0 errors. Elapsed time: 0 hours 21 minutes 46 seconds. Internal database snapshot has split point LSN = 000ab776:0000112f:0001 and first LSN = 000ab776:0000112d:0001.

I ran DBCC PAGE but it errors (doesn't even seem to be returning the right page in the first place). I CAN run it with print option 2 and it returns but honestly I don't know what I'm looking for there.

DBCC PAGE ('MyDatabase',1,745345,3)

PAGE: (3:513793)

BUFFER:

BUF @0x00000003811F8280

bpage = 0x00000000F2D70000 bhash = 0x0000000000000000 bpageno = (1:745345)
bdbid = 5 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 44283 bstat = 0x809
blog = 0x5adb215a bnext = 0x0000000000000000

PAGE HEADER:

Page @0x00000000F2D70000

m_pageId = (3:513793) m_headerVersion = 1 m_type = 2
m_typeFlagBits = 0x4

This answer is taken from an issue of the SQLskills.com newsletter written by Paul Randal, about "a database which would fail a backup with page checksum errors, but passed a DBCC CHECKDB".


The only time this can happen is when an extent is a mixed extent
(where the 8 pages in the extent can be allocated to potentially 8
different allocations units – see here) and some pages are
erroneously marked as allocated by the relevant PFS page.


When that happens, DBCC CHECKDB will not attempt to read those pages,
as it derives what pages to read from an allocation unit's IAM pages
(the first of which lists the pages allocated from a mixed extent).
This case is a gap in DBCC CHECKDB's corruption-detection logic.


[Because] DBCC CHECKDB couldn't detect the corruption, it wasn't
possible for it to make the repairs needed to fix them.
So using DBCC WRITEPAGE, I worked out the changes needed in the allocation status
for the erroneously-allocated pages, directly in the PFS page, and it
worked!


This was an extremely rare case – it's much more common that a DBCC CHECKDB
fails but a backup would succeed.

In my opinion, Paul's resolution is way above and beyond exporting and importing the data like you did, so I think you did the right thing.

StackExchange Database Administrators Q#211192, answer score: 9