Recovery methodsTechnical

Database Page-Level Corruption and Transaction Recovery

Database files are structured page stores whose current state depends on both the pages themselves and their transaction history — page checksums, allocation metadata and logs have to be interpreted together, not treated as one binary “file is corrupt” verdict.

A database file is a page store, not an arbitrary blob

Relational database engines organize persistent data into fixed-size pages, with a typical internal structure running from a file header through allocation metadata into the pages themselves, then page headers, then the rows, records and indexes those pages actually hold. Recovery should distinguish several separate questions rather than one: is the file readable; is a given page structurally valid; does that page belong to the object it claims to; are indexes and trees consistent; is transaction state consistent; and is the business data itself logically coherent. These are not the same question, and a database can pass some of them while failing others.

Corruption is usually localized to specific pages, not the whole file

SQL Server's DBCC CHECKDB checks physical page consistency, logical consistency, rows, allocation pages, index relationships, system-table integrity and other structural relationships as separate concerns. A corruption report is therefore very rarely just “the file is corrupt” — it can identify a specific, much narrower object-and-page relationship, which is exactly what makes page-level recovery possible in the first place rather than forcing a full rebuild.

Page checksums turn corruption into a precise recovery unit

SQL Server can detect checksum failures, torn writes and other I/O-related page corruption, and can track a damaged page's exact file ID and page ID. That precision is what makes isolated recovery possible at all: instead of treating an entire multi-gigabyte database file as equally suspect, the engine can point at one specific page as the actual unit that needs attention.

Write-ahead logging is what makes crash recovery possible

Both SQL Server's transaction log and MySQL's InnoDB redo log exist so that a modification is durably recorded before it is considered committed, which is what lets an engine recover cleanly after a crash: replaying logged changes from the last checkpoint forward, and rolling back whatever transactions never completed. A page on disk can legitimately be older than the database's true committed state until this redo process runs — that gap is normal operation, not damage.

Restore-and-roll-forward is recovery from history, not byte repair

Isolated page restore, as SQL Server documents it, restores an affected page from backup and then applies transaction-log records to bring that page forward to a state consistent with the rest of the current database. This is recovery built from backup plus log history, not an attempt to patch bytes directly inside the damaged file — a distinction worth stating explicitly, since the two are easy to conflate but behave very differently.

Authoritative rows and derived indexes are not the same risk

A secondary or nonclustered index can often be rebuilt from the underlying heap or clustered index that it was derived from in the first place, which makes that category of corruption meaningfully less serious than damage to the clustered index or base table rows themselves, the system tablespace, or engine dictionary metadata. Corruption in authoritative row storage risks losing data outright; corruption in a structure that can be regenerated from that row storage generally does not.

Repair and restore solve different problems

Restoring from a known-good backup plus log history is a fundamentally different action from an engine's built-in repair command, which typically restores structural consistency by deallocating or discarding whatever it cannot reconcile. “Repaired successfully” means the engine considers the structure consistent again — it does not mean every original record survived the process intact.

Corruption evidence can point at the storage layer underneath

Database engines themselves note that corruption can originate in the filesystem, hardware, drivers, storage cache, the SAN or I/O path, memory, or the database engine itself. If consistency-check results vary between repeated runs against the same file, that pattern specifically suggests a transient I/O, cache or memory problem rather than a fixed, reproducible structural defect — which is exactly why this cluster connects directly back into RAID, SAN and general storage-layer diagnosis.

A byte-perfect file copy is not automatically a consistent backup

Copying individual database files from a live, actively writing system can capture one data file from before an update, another from after it, and a log at yet another point in time — every byte can copy without a single I/O error, and the resulting set still never existed together as one real transactional state. Database-consistent backup requires a native backup mechanism, a properly quiesced snapshot, or an application-consistent storage snapshot; a plain file copy of a live database is not a substitute for any of those.

Practical rule

Database recovery does not end when the server successfully opens the file. Physical readability, structural consistency, transactional consistency and business-logic validity are separate checks, and each one needs its own verification.