Backup detects corruption, but CHECKDB does not

Question

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 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 1075937538 m_indexId (AllocUnitId.idInd) = 2
Metadata: AllocUnitId = 633462595911680 Metadata: PartitionId = 0
Metadata: IndexId = -1 Metadata: ObjectId = 0 m_prevPage = (3:513795)
m_nextPage = (3:513820) pminlen = 17 m_slotCnt = 426
m_freeCnt = 2 m_freeData = 7338 m_reservedCnt = 0
m_lsn = (608841:643611:411) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 0 DB Frag ID = 1
Allocation Status
GAM (1:511232) = ALLOCATED SGAM (1:511233) = NOT ALLOCATED 
PFS (1:744096) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:511238) = NOT CHANGED
ML (1:511239) = NOT MIN_LOGGED 

Msg 2514, Level 16, State 8, Line 20
A DBCC PAGE error has occurred: Invalid page metadata - dump style 3 not possible.

Any ideas what I could try next? Server version is

select @@version

Microsoft SQL Server 2014 (SP2-CU11) (KB4077063) - 12.0.5579.0 (X64) 
 Feb 21 2018 12:19:47 
 Copyright (c) Microsoft Corporation
 Developer Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

Compatibility level of the DB is 100 (SQL 2008).

• Paul White: Comments on this question have been moved to chat.

Answer 1

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.