A corrupted MDF file is one of the more stressful things a database administrator can run into. The MDF is the primary data file for a SQL Server database, so when it will not attach, throws I/O errors like 823 or 824, or leaves the database marked “Suspect,” the data inside can feel locked away. The good news is that corruption rarely means the data is gone. In most cases it can be recovered, and the right path depends almost entirely on one question: do you have a clean backup? For background, see our recent coverage.
This guide walks through both scenarios, with the actual steps and commands, so you can work from the safest option down to the last resort.
First, confirm it is really corruption
Before touching anything, take a copy of the MDF (and its matching LDF log file) and set it aside. Every recovery attempt should be made against a copy, never the only surviving file. Then run a consistency check so you know what you are dealing with:
DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;
The output tells you how many allocation and consistency errors exist and, importantly, the minimum repair level SQL Server thinks is required. Note that number down. If the check reports no errors, the problem may be a detached or permission issue rather than true corruption, which is a much easier fix.
Recovering with a backup (the clean path)
If you have a recent, verified backup, this is almost always the fastest and safest route, because you are restoring known-good data rather than trying to repair damaged pages. Put the database into single-user mode, then restore:
RESTORE DATABASE YourDatabase FROM DISK = 'D:BackupsYourDatabase.bak' WITH REPLACE, RECOVERY;
If you also have transaction log backups, restore the full backup WITH NORECOVERY first, then apply the log backups in order to roll the database forward to the most recent point in time. This is where a disciplined backup chain pays off: you can often recover to within minutes of the failure. Microsoft’s own RESTORE documentation covers the exact syntax for point-in-time and piecemeal restores.
Recovering without a backup
This is the situation most people are actually in when they go searching for help. No usable backup, a database that will not come online, and pressure to get it back. You still have options, but each one carries more risk than the last.
Start by trying to bring the database up in emergency mode so you can at least read from it:
ALTER DATABASE YourDatabase SET EMERGENCY;
ALTER DATABASE YourDatabase SET SINGLE_USER;
If the database becomes readable, script out or export whatever data you can immediately, before attempting any repair. If it will not read, the documented last-resort command is:
DBCC CHECKDB ('YourDatabase', REPAIR_ALLOW_DATA_LOSS);
The name is not a warning to ignore. As Microsoft’s DBCC CHECKDB reference makes clear, REPAIR_ALLOW_DATA_LOSS can deallocate corrupted pages to make the database consistent again, and anything on those pages is permanently lost. It should only be run against a copy, and only when you have accepted that some rows may not survive.
When a dedicated recovery tool makes sense
There is a gap between “restore from backup” and “accept data loss,” and that gap is where specialist software earns its place. If DBCC cannot bring the database to a consistent state, if REPAIR_ALLOW_DATA_LOSS would cost records you cannot afford to lose, or if the MDF will not attach at all, a purpose-built repair tool can often read the raw file structure and extract tables, keys, stored procedures and triggers that native commands give up on.
Tools like Stellar’s are designed exactly for this: they open a damaged MDF directly, let you preview recoverable objects, and save them to a new healthy database or script. For teams that need to recover sql server database files where no backup exists, it is usually the difference between losing a table and losing a business day. It is not a replacement for good backups, but it is a strong safety net when the usual paths run out.
Make the next time easier
Once you are back online, close the loop so this is not a recurring fire drill. Schedule full and transaction-log backups that match your tolerance for data loss, and test a restore from them, because an untested backup is only a hope. Run DBCC CHECKDB on a regular job so corruption is caught early rather than discovered during an outage. And keep SQL Server on reliable storage with stable power, since sudden shutdowns and failing disks cause the majority of MDF corruption in the first place.
Corruption feels like an emergency, and it is, but it is a solvable one. Work from backups if you have them, fall back to emergency mode and DBCC if you do not, and keep a proven recovery tool on hand for the cases in between.