Sql-server – MSSQL error: consistency-based I/O error – can it be caused by an MSSQL or OS problem

That assertion happens because the restore code read a page from the backup but the page is corrupt and the file ID stamped in the page header doesn't exist in the database being restored. It's firing from a code file called bckioreq.cpp (I used to own all this stuff while at MS).

The message to run DBCC CHECKDB is a generic message that doesn't apply in this case.

I take it you're restoring a full database backup and then a series of other differential and/or log backups? You're restoring on 2005, but are you restoring an older backup?

This is what's called a retail-assert in the code - there's absolutely no way to get around it - as soon as the code hits it, the assertion will fire and the restore bombs out. There's a Connect item to make this nicer but it's not fixed in 2008 either.

Is this happening on the full backup you're restoring or one of the subsequent differential and/or log backups? If the full backup, there's nothing you can do - that backup is toast. If one of the later backups, you can restore everything up to but not including that backup.

That's basically your answer I'm afraid.

Now, how did this happen? (rhetorical question) It could be that the database that was backed up was corrupt, or that the I/O subsystem corrupted the backup. Couple of things you can do to help protect against this - turn on page checksums in the database and use the WITH CHECKSUM option on your backups. This adds some checking to make sure what's being backed up isn't corrupt. You can also validate your backups in various ways - checkout my blog post on this: Importance of validating backups.

Hope this helps!

Your recovery solution is the text book way to proceed. Assuming you have appropriate backups and provided you can backup the transaction log for the corrupt database, then your strategy is the text book one to implement.

Before proceeding however have you considered the possibility of recreating solely the affected table?

Sometimes you can get away with creating an exact copy of the affected table by doing a

select *
into NewTableFromOld
from DamagedTable

Then just drop/swap the damaged table with the new, remembering to add appropriate constraints and indexes.