Sql-server – What utilities can I use to simulate I/O load for a DB restore and DBCC check

The following is a compilation of results that I read up on. You will find vastly more information in the linked blogs and documents.

First, it can happen that DBCC CHECKDB won't detect inconsistencies if you turn off checksum or torn_page verification. A quote from Paul Randal:

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.

The above situation can hit you, if you upgraded a database from SQL Server 2000 or before to 2005 or later. You then need to manually enable page checksums with ALTER DATABASE to get them active. But then the 2nd paragraph of the above quote kicks in and might trouble you.

BACKUP WITH CHECKSUM will detect checksum inconsistencies, but only if the page already had a checksum written to it, when it is being backed up. Normally DBCC CHECKDB also detects these errors, so it's not a good idea to "use BACKUP WITH CHECKSUM to replace DBCC CHECKDB".

Now there is a second possibility for DBCC CHECKDB not to show any inconsistencies, even if there are some. For this I'm just quoting again Paul Randal in "Misconceptions around corruptions: can they disappear?":

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.

I don't have a final answer to your question, but as DBCC CHECKDB only checks allocated pages it won't show inconsistencies in deallocated pages. The only situation I can imagine now is that BACKUP also backups those deallocated pages showing potential checksum errors that were skipped by DBCC CHECKDB.

The operating system returned error 23(Data error (cyclic redundancy check).) to SQL Server during a read

This indicates the operating system detected an issue reading data from the hard disk itself. When data is written onto the filesystem, the operating system calculates a CRC code for each block written; when that data is subsequently read back the operating system performs the CRC operation again on the read data and compares it with the CRC stored in the filesystem. If these two CRC codes don't match the OS reports error 23. I'd be very surprised if there isn't a hardware issue with either the drive itself, or perhaps some other component such as the motherboard or drive controller.

WITH (ONLINE=ON) in the Enterprise Edition of SQL Server will have no effect whatsoever on this issue. Online index operations vs offline index operations are no different at the operating system layer; data is read and written as necessary to either rebuild or recreate the index.