Bits of .NET

🗄️ Before You Restore From Backup, Run This One Command

A query suddenly throws a corruption error – torn page, checksum mismatch – and the instinct is to panic and start restoring from last night’s backup immediately, losing a full day of transactions. There’s a faster, less destructive first step almost everyone skips.

🐞 The Problem

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error:
incorrect checksum. Additional messages in the SQL Server error
log may provide more detail.

🔍 Why This Happens

Corruption almost always comes from the storage layer, not SQL
Server itself - a failing disk, a bad RAID controller, an
interrupted write during a power loss, or (in virtualized/cloud
environments) a storage subsystem glitch. SQL Server's page
checksums are specifically designed to CATCH this kind of damage
rather than silently return corrupted data, which is why the error
appears the moment a damaged page is read.

✅ The Fix: Diagnose the Actual Damage Before Choosing a Recovery Path

• Run DBCC CHECKDB('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS immediately — this reports exactly which objects and pages are affected, which tells you whether this is one damaged index (cheap to fix) or something systemic (needs a full restore).
• If the damage is isolated to a NONCLUSTERED index, you can often just drop and rebuild that index — zero data loss, since the index is fully derivable from the underlying table data.
• Only reach for DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS as a genuine last resort when no clean backup exists — as the name says, it can discard damaged rows to restore consistency, so a restore from a good backup is almost always safer when one is available.

🚨 What Not to Do First

• Don’t run REPAIR commands blind, before CHECKDB has told you the actual scope — you could be discarding data unnecessarily when a much smaller fix (like an index rebuild) would have fully resolved it.
• Don’t keep writing to the damaged database while diagnosing — further writes can spread the damage or make recovery options worse. Take a backup of the CURRENT (corrupted) state first, even if it’s just for forensics.