MDF File Recovery: How to Repair a Corrupted SQL Server MDF File and Get the Data Back

When SQL Server reports that a database is damaged, refuses to attach an MDF file, or puts the database into SUSPECT, the instinct is to reach for a repair command or an old backup straight away. Both can cost data that was still recoverable. A SQL Server MDF file is not one fragile block: it is made of thousands or millions of 8 KB pages, and in most corruption and ransomware cases we see, the large majority of those pages are still intact.

This guide explains what is inside an MDF file, what the common corruption errors point to, what to do and not do first, and how MDF file recovery works when SQL Server itself cannot open the database.

The MDF is the primary data file of a SQL Server database; NDF files are secondary data files of the same database, and the LDF is its transaction log. Every data file is divided into 8 KB pages. Each page has a 96-byte header that records which file and page number it is, which object and index it belongs to, what type of page it is (data, index, large-object, allocation) and, on most databases, a checksum of its content.

A few pages near the start of the file describe the database as a whole: the file header (page 0), the allocation maps that record which pages are in use, and the boot page (page 9) that holds the database name, version and the location of the system catalog. The system catalog is itself stored in ordinary pages: it lists every table, its columns and data types, and which pages hold its rows.

Two things follow for recovery. First, damage to some pages does not damage the others: each intact page can still be read and checked on its own. Second, the pages at the start of the file matter more than their size suggests, because they are the map. When they are damaged, SQL Server cannot open the database even if nearly all of the data is still there.

Error 823 means Windows reported a read failure: the problem is below SQL Server, in the disk, the storage path or a file that has been altered by something else. Error 824 means the read succeeded but the page failed SQL Server's own check: a wrong checksum, a torn page, or a page that carries a different page number than expected. Error 5172 or a 'not a primary database file' message on attach means the file header itself is not valid, which is typical when the start of the file has been overwritten or encrypted.

DBCC CHECKDB reports the same problems page by page, with allocation errors (pages in use that the maps do not know about, or the reverse) and consistency errors (records or links inside pages that do not make sense). A long CHECKDB report is not a sign that everything is lost; it is a list of the specific pages that are.

Stop the SQL Server service or take the database offline, then copy the MDF, every NDF and the LDF to a separate disk, and work only on the copies. Repair commands, attach attempts with a rebuilt log and restores over the original location all change the files. An untouched original keeps every recovery option open, including a laboratory one.

Check the backups next. A recent full backup with its log backups is still the cleanest way back, but restore it to a different database name or a test server first, so a failed restore does not overwrite the damaged files you may still need.

DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS does exactly what its name says. To make the database consistent again it deallocates pages it cannot fix and removes the records that point to them, so whole sets of rows can disappear, and nothing reports which customer, invoice or ledger entries they were. Emergency mode followed by this repair is a common way a database that was mostly recoverable ends up mostly empty.

Use it only on a copy, only after the data has been read out another way, and only when a consistent database matters more than the rows it will remove.

Recovery does not need SQL Server to accept the database. The file is read page by page, directly from the copy: each page is identified by its header and checked, and the system catalog pages are decoded to rebuild the list of tables, columns and data types. The data pages of each table are then decoded record by record, using that table definition, into ordinary rows that can be written into a new, healthy database or exported to files.

When the catalog itself is damaged, the definition of a table can still be worked out from the surviving catalog fragments, from constraint and index names, or from a reference database with the same structure, such as an older copy or the same application installed for another company. Pages that were overwritten or encrypted are reported as lost, row counts are checked against the catalog wherever it survives, and the result says plainly which tables are complete and which are not.

A detail that matters on older databases: databases set to TORN_PAGE_DETECTION instead of CHECKSUM overwrite the last two bits of every 512-byte sector of a page with a marker and keep the original bits in the page header. A tool that does not put those bytes back reads garbage table names and rows from pages that are actually fine. On one 188 GB database we recovered, restoring the torn bits took the number of damaged pages from over 400,000 to zero.

Large database files are exactly what many ransomware families encrypt only partly, to finish quickly: often the first few hundred kilobytes or megabytes, sometimes stripes through the file, plus a marker or footer at the end. The start of the file holds the header and boot page, so SQL Server refuses the whole database, while most data pages behind it are untouched.

In that situation the data is read from the unencrypted pages, and the missing header pages are rebuilt or taken from a reference copy. In one LockBit case, ERP backups encrypted by the ransomware were recovered to 354 tables and 98.8% of the rows, checked against a healthy copy of the same database. How much can be recovered always depends on how much of the file was encrypted, which is measured before any promise is made. We never claim to decrypt files without the key.

If the database matters and you have an untouched copy, you can scan it yourself with AS SQL Recovery & Repair Studio: the trial analyses the MDF, NDF or BAK file, shows every table and row it can recover and exports the first rows of each table, so you can see the result before buying. If the database files were deleted or are somewhere on an encrypted or formatted disk, the free AS SQL Deleted Database Recovery tool finds them and packs them into one encrypted image for our laboratory.

For urgent or business-critical systems, our SQL Server database recovery service works remotely or in our laboratory and gives you a free assessment of exactly what can be recovered before any work is paid for.