If your SQL Server database won’t attach or shows corruption, start with the safest path: restore a clean backup. If you have no backup and corruption looks minor, CHECKDB with REPAIR_REBUILD can help in single-user mode. If DBCC requires REPAIR_ALLOW_DATA_LOSS, expect data loss and compare a repair tool against what DBCC can salvage.
Quick Answer (Recommended)
- Have a recent backup: restore it first for the safest recovery.
- Minor corruption: run CHECKDB with REPAIR_REBUILD in single-user mode.
- DBCC requires REPAIR_ALLOW_DATA_LOSS: Expect data loss. If you need maximum recovery, evaluate a repair tool and compare results against what DBCC can salvage.
Emergency Path (Summary - Last Resort)
- Back up and work on copies of the MDF/NDF/LDF files.
- Set EMERGENCY + SINGLE_USER to regain access.
- Run the repair, return to MULTI_USER, then validate and back up.
Note: Use this only when you cannot restore from a clean backup.
Method comparison (fast decision)
| Method | Best for | Data loss risk | Time | Needs SQL Server? |
|---|---|---|---|---|
| Restore from backup | Clean recovery | None | Fast | Yes |
| DBCC CHECKDB (REPAIR_REBUILD) | Minor corruption | Low | Medium | Yes |
| DBCC CHECKDB (REPAIR_ALLOW_DATA_LOSS) | Severe corruption, no options | High | Medium | Yes |
| Repair tool | Severe corruption / no backup | Lower (results vary) | Faster | Not for scan; live export may need SQL Server |
Before you start (don’t skip this)
- Take a verified backup: repair operations can cause data loss.
- Work on a copy: keep original MDF/LDF files untouched when possible.
- Ensure disk space: repairs can create new database files.
- Plan for exclusive access: DBCC requires SINGLE_USER mode and active sessions must be closed.
Common scenarios (pick yours)
SUSPECT / RECOVERY_PENDING
- Go to Method 2 (Emergency mode) if restore is not possible.
- Check storage and I/O errors first to avoid recurring corruption.
MDF will not attach / missing LDF
- Restore from a clean backup when possible.
- Rebuilding the log can cause data loss, so treat it as a last resort.
- If you must attempt attach-without-log, test on a copy first and expect loss; restore is safer.
DBCC shows 823/824/825
- Treat it as an I/O path issue before repairing.
- Fix storage/drivers, then restore or run CHECKDB.
Error 9004 (log issue)
- Check log file health and disk space.
- Restore from backup if the log is damaged.
Understanding MDF files in SQL Server
SQL Server stores database data in data files and log files. The primary data file typically uses the .mdf extension, transaction logs use .ldf, and optional secondary data files often use .ndf. The primary data file contains startup information and the bulk of user data.
Reasons behind MDF file corruption
- Bad sectors or failing storage where MDF/LDF files reside.
- Sudden power outage or forced shutdown during write operations.
- Virus or malware interference with database files.
- Improper SQL Server shutdown or crash while the database is open.
Common error messages displayed when MDF is corrupted
When MDF corruption occurs, SQL Server may surface I/O and consistency errors. Below are the most common ones and what they usually indicate.
Error 823 (I/O error): SQL Server could not read/write a page because the OS reported an I/O failure.
- Check Windows Event Logs and storage/controller drivers.
- Run CHECKDB to assess the scope of corruption.
- Fix the storage path before attempting repairs.
Error 824 (logical consistency I/O error): Often a checksum or torn-page error. This is the classic Fix SQL Server error 824 case.
- Restore from a clean backup if available.
- Enable PAGE_VERIFY CHECKSUM to reduce future corruption risk.
- Use CHECKDB to see the minimum repair level.
Error 825 (read-retry warning): SQL Server had to retry a read. It is a warning sign of underlying I/O problems.
- Investigate disks, controller, and drivers even if the query succeeded.
- Consider running CHECKDB to ensure no hidden damage.
Error 9004: Transaction log or log processing error. This often points to log file issues or storage problems.
- Check log file health and disk space.
- Restore from backup if the log is damaged.
How to repair MDF files?
Choose the safest path first: restore from a clean backup. If that’s not possible, use CHECKDB or a repair tool based on the severity of corruption. For broader recovery planning, see our SQL Server database repair guide.
Start Here (Recommended): Fix the cause + restore from a known good backup
Microsoft guidance is to resolve the underlying I/O, storage, or driver issue first, then restore from a known good backup. Repairs are a last resort because they can remove data to make the database consistent.
Decision box
- Have a clean backup? Restore it, then run DBCC CHECKDB to confirm integrity.
- No backup possible? Run CHECKDB (no repair) to see the minimum repair level it recommends.
- Seeing 823/824/825? Treat this as an I/O path problem first (storage, drivers, event logs).
Method 0: Restore from Backup (Recommended)
This is the safest and most reliable way to recover a SQL database, especially for production systems.
- Resolve storage or I/O issues before restoring to avoid repeated corruption.
- In SSMS, go to Databases > Restore Database and select your latest clean full backup.
- After restore, run CHECKDB to confirm database integrity.
Method 1: Repair MDF File Using DBCC CHECKDB Command
Important: Before running any repair, make physical copies of MDF/NDF/LDF files.
- Run CHECKDB without repair first and follow the minimum repair level recommended.
- Repairs are a last resort; Microsoft recommends restore over repair when possible.
DBCC CHECKDB best practices (real-world)
- Run during low-traffic windows; it can be resource heavy.
- Always run CHECKDB without repair first.
- Prefer restore over repair when possible.
- After repair, run integrity and constraints checks and take a full backup.
Step 1: Run DBCC CHECKDB
In SSMS, run:
DBCC CHECKDB ('YourDatabaseName')Use the output to confirm the minimum repair level.
Step 2: Assess and plan
If CHECKDB recommends a specific repair level, use that before REPAIR_ALLOW_DATA_LOSS.
Step 3: Minimize operational impact
Switch to single-user mode:
ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;Step 4: Execute the repair
Minimal risk:
DBCC CHECKDB ('YourDatabaseName', REPAIR_REBUILD);Last resort (data loss possible):
DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);Caution: REPAIR_ALLOW_DATA_LOSS is a last resort. Always take a backup first.
After REPAIR_ALLOW_DATA_LOSS: Run DBCC CHECKCONSTRAINTS and validate business logic. Referential integrity and constraints can be affected.
When NOT to use REPAIR_ALLOW_DATA_LOSS
- You have a clean backup that can be restored.
- DBCC suggests REPAIR_REBUILD or less invasive options.
- Data loss is unacceptable for production or compliance needs.
After any repair: Take a full backup immediately; it resets the log chain.
Step 5: Restore database accessibility
Return to multi-user mode:
ALTER DATABASE YourDatabaseName SET MULTI_USER;Step 6: Verify and review
Re-run CHECKDB to confirm integrity:
DBCC CHECKDB ('YourDatabaseName')Cleaner output:
DBCC CHECKDB (N'YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;Spot-check critical queries.
Method 2: Emergency mode repair (last resort if you cannot restore)
Use this when a SQL database is marked as SUSPECT or you need to recover a SQL database from a pending state and restore is not possible.
Warning: Emergency mode repair can cause data loss and cannot be rolled back.
- Set EMERGENCY mode:
ALTER DATABASE YourDatabaseName SET EMERGENCY; - Set SINGLE_USER mode:
ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; - Run repair:
DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); - Set MULTI_USER mode:
ALTER DATABASE YourDatabaseName SET MULTI_USER; - Run
DBCC CHECKCONSTRAINTS, validate data, and take a full backup immediately.
Prevent corruption after recovery
- Fix disks, controllers, and drivers before returning to production.
- Enable PAGE_VERIFY CHECKSUM to detect page corruption early.
- Schedule DBCC CHECKDB during low-traffic windows.
- Use UPS and clean shutdown practices to avoid sudden power loss.
- Monitor storage health and error logs proactively.
Instant Solution - SysCurve SQL Repair Tool
When restore or DBCC fails, quickly repair MDF and NDF files.
- Works when DBCC fails or corruption is severe.
- Recovers objects with preview before saving.
- Export to a new or live database.
Method 3: Repair MDF files using MDF File Repair Tool
If manual repair attempts with the DBCC CHECKDB command are unsuccessful, or if you’re seeking a simpler, more user-friendly approach to mend corrupt SQL Server MDF files, the SysCurve MDF Repair tool offers a robust solution. This software simplifies the repair process and minimizes the risk of data loss, making it an attractive option for users of all skill levels. Here’s a step-by-step guide on how to use this tool to repair your MDF files effectively:
Note: Before proceeding with any repair process, ensure you have a recent backup of your database. This precaution safeguards your data against any unforeseen issues that might arise during the repair process.
- Download, install and open the MDF repair tool.
- Click the Browse button and select the desired MDF file. If you don’t know where the file is, click the Find button, select the drive, and click the OK button. The software will search all MDF files in the selected drive.
- Choose the MDF file, select the Include Deleted Records checkbox, and click on the Repair button.
- Choose the Standard scan option and click on the OK button.
- After the process, the software will display all recovered items on the left pane.
- Select any table or database object to see a preview within the software window.
- Click the Save button and select MDF as the Save option
- Choose a New or Live database as per your requirement.
- Enter Server name/Instance name and choose the authentication mode
- Click on the Save button
Using the SysCurve MDF Repair tool, you can efficiently repair corrupt SQL Server MDF files. This method is particularly useful for those who prefer a straightforward, automated approach over manual repair methods. The tool’s ability to include deleted records and provide a preview of recoverable data before saving ensures a comprehensive recovery process, making it a reliable solution for SQL MDF file repair needs.
Other Helpful Features
- Repair MDF and NDF files.
- Recover tables, indexes, and triggers.
- Preview recovered objects before saving.
- Save to MDF, XLS, CSV, or HTML.
- Compatible with major SQL Server versions.
Frequently Asked Questions
Can I use this tool to recover accidentally deleted records?
Yes. SysCurve MDF repair tool provides an option to include deleted records and recover them to a new table.
Can MDF file repair tool repair data from all associated NDF files?
Yes. Keep all related NDF files in the same location as the MDF during recovery.
Does this tool come with a free demo version?
Yes. The demo lets you repair and preview data; saving requires the full version.
Are there free MDF repair tools?
Microsoft’s built-in option is DBCC CHECKDB. Third-party repair tools are typically paid, but many offer trials for evaluation.
Is there a free MDF file viewer?
If the database is healthy, SQL Server Management Studio can open and browse it. For corrupted MDF files, repair tools are usually required.
Can I attach a corrupted MDF file without LDF?
You can sometimes rebuild the log (FOR ATTACH_REBUILD_LOG), but Microsoft warns this can cause data loss. Use backup restore or a repair tool if corruption is severe.
The Final Word
Start with a clean backup whenever possible. Use CHECKDB for minor corruption, and reserve REPAIR_ALLOW_DATA_LOSS or emergency mode for last-resort recovery.
If manual methods fail or the database is SUSPECT, a repair tool can provide a safer path to recover critical data.
