SQL Server and MySQL Database Recovery: MDF, InnoDB and Suspect Mode

Quick answer: If your database will not open or has dropped into suspect mode, do not panic-run a DROP or REPAIR command. First make a safe copy of the database files (MDF/LDF or InnoDB pages). In SQL Server, DBCC CHECKDB maps the damage; in MySQL, InnoDB recovery levels are tried. But repair commands can lose data. Professional support is essential for a critical database. DSET recovers databases at its Ankara Hacettepe Technopark Beytepe lab: +90 536 662 38 09.

How do database files work?

Microsoft SQL Server keeps its data in two main files: the MDF (primary data file) and the LDF (transaction log). The MDF holds your tables and indexes, while the LDF records not-yet-committed transactions to ensure consistency. Together they form a consistent whole. Losing the LDF often leaves the MDF half-finished.

On the MySQL side there are two main storage engines. InnoDB is the modern default, offering transaction support (ACID) and lock management, keeping its data in ibdata files and per-table .ibd files. MyISAM is older and simpler, storing each table separately as .MYD (data) and .MYI (index). Their corruption behaviour differs greatly.

System Data file Log/index Transactions
SQL Server MDF LDF Yes
MySQL InnoDB ibdata, .ibd redo log Yes (ACID)
MySQL MyISAM .MYD .MYI No

What is suspect mode and page corruption?

SQL Server runs a consistency check while opening a database. If the LDF cannot be read or recovery cannot complete, it marks the database SUSPECT and blocks access. This is a protection mechanism: the system prefers not to run on corrupt data.

Page corruption is sneakier. SQL Server stores data in 8 KB pages. When a disk error, controller fault or sudden shutdown corrupts a page, queries touching that page fail but the database as a whole may stay up. DBCC CHECKDB exists precisely to map this page-level damage.

The danger of DBCC CHECKDB and repair commands

DBCC CHECKDB is SQL Server's built-in consistency checker. It scans logical and physical integrity and reports corrupt pages and indexes. It is safe up to here because it only reads and reports.

The danger lies in the repair options. The REPAIR_ALLOW_DATA_LOSS parameter, as its name says, allows data loss. If it cannot repair corrupt pages, it deletes them. This command sometimes opens the database but can destroy critical records inside. Microsoft recommends it as a last resort and only after taking a backup. Running it blindly on a critical production database is a classic example of mistakes that destroy data in recovery.

On the MySQL InnoDB side the equivalent tool is the innodb_force_recovery levels. Recovery is attempted at increasing aggressiveness from 1 to 6, but levels 4 and above can permanently corrupt data. In MyISAM, the myisamchk tool repairs the .MYI index.

Why does a database on RAID need two-layer recovery?

Enterprise databases almost always run on a RAID array. In RAID 5 or RAID 10 arrays data is distributed across multiple disks. When the array collapses because a disk fails, the RAID level must be recovered first.

Here is the critical point: RAID recovery and database recovery are two separate layers. You must first rebuild the array with the correct parameters (stripe size, disk order, parity rotation) to get a consistent block-level image. Only if that image is correct do the MDF or InnoDB files on top make sense. If the RAID is reconstructed wrongly, the database files look corrupt and people waste time on DB repair. That is why RAID-on-database cases require expertise. For the basics see our what is data recovery guide.

Why are backups and transaction logs so important?

The best database recovery is the one that is never needed. On a system with regular full backups plus transaction log backups, you can roll back to a specific moment (point-in-time recovery) when corruption strikes. If the LDF chain is intact you can replay transactions from the last consistent point.

So our rule is clear: when a database corrupts, never touch, delete or move the LDF or redo log files. Many recoverable cases become unrecoverable because a panicking person deleted the log file.

Frequently Asked Questions (FAQ)

How do I open a database in suspect mode? First copy the files. Setting it to EMERGENCY mode and running DBCC CHECKDB to see the damage is the first step, but REPAIR_ALLOW_DATA_LOSS loses data. Consult a specialist for critical data.

I have the MDF but lost the LDF, can it be recovered? In most cases yes, SQL Server can rebuild the database from the MDF without the LDF, but there is a consistency risk. Uncommitted transactions may be lost.

My MySQL ibdata file corrupted, what should I do? First stop the server and copy the entire data folder. innodb_force_recovery is tried from a low level, but high levels are dangerous. If possible, export the data with mysqldump.

DBCC CHECKDB returned errors, is my database gone? No. CHECKDB only reports, it does not delete. The page numbers in the error message guide the recovery strategy. The real danger is in the repair command.

Do you charge if no data is recovered? No. At DSET the first diagnosis is free and no fee is charged if data cannot be recovered. For privacy and process read our security article.

Sources