BACKUP 'BrokenDatabase' detected an error on page (1:123456) in file ’BrokenDatabase.mdf'. Error: 3043,Severity: 16,State: 1.
在链接问题和备份中我已准备好进行DBCC PAGE调查,DBCC CHECKDB传递没有错误,但显然存在损坏.
CHECKDB将通过哪些类型的损坏但是BACKUP WITH CHECKSUM会失败？
解决方法
首先,如果关闭校验和或torn_page验证,DBCC CHECKDB将不会检测到不一致. Paul Randal在this post中的一句话：
You’re right – if torn-page or checksum isn’t turned on then there’s nothing
that can be detected as far as page protection options are concerned.
CHECKDB may still pick up on corruptions that it finds from doing all the
consistency checks that it does – but it won’t see corruptions in the middle
of data values,for instance.Ha – that’s the bummer about turning on page checksums – nothing happens
until a page is read in,changed,and written back out. The only way to
force pages to get checksums is to make them change – e.g. through
rebuilding all your indexes,whcih may be unpalatable – there’s no ‘touch’
tool out there at all.
如果您从sql Server 2000或之前升级到2005或更高版本,则上述情况可能会对您造成影响.然后,您需要使用ALTER DATABASE手动启用页面校验和以使其处于活动状态.但是上面引用的第2段开始并且可能会给你带来麻烦.
使用CHECKSUM进行备份将检测校验和不一致,但前提是该页面在备份时已经写入了校验和.通常,DBCC CHECKDB也会检测到这些错误,因此它是not a good idea to use BACKUP WITH CHECKSUM to replace DBCC CHECKDB.
现在,DBCC CHECKDB有第二种可能性,即使有一些不一致,也不会显示任何不一致.为此,我在Misconceptions around corruptions: can they disappear?再次引用Paul Randal：
So what about the disappearing corruptions? This gets into how consistency checks work. Consistency checks only run on the pages in the database that are allocated. If a page isn’t allocated to anything,then the 8192 bytes of it are meaningless and can’t be interpreted. Don’t get confused between reserved and allocated – I explain that in the first misconceptions post here. As long as a page is allocated,it will be consistency checked by DBCC CHECKDB,including testing the page checksum,if it exists. A corruption can seem to ‘disappear’ if a corrupt page is allocated at the time a DBCC CHECKDB runs,but is then deallocated by the time the next DBCC CHECKDB runs. The first time it will be reported as corrupt,but the second time it’s not allocated,so it isn’t consistency checked and won’t be reported as corrupt. The corruption looks like it’s mysterIoUsly vanished. But it hasn’t – it’s just that the corrupt page is no longer allocated. There’s nothing stopping sql Server deallocating a corrupt page – in fact,that’s what many of the DBCC CHECKDB repairs do – deallocate what’s broken,and fix up all the links.
我没有对您的问题给出最终答案,但由于DBCC CHECKDB仅检查已分配的页面,因此在解除分配的页面中不会显示不一致.我现在能想象的唯一情况是BACKUP还备份那些显示DBCC CHECKDB跳过的潜在校验和错误的解除分配的页面.
