SQL Server MDF Corrupted? Database Repair Field Guide
Data Recovery 2026-10-08 CentralComputer Team

SQL Server MDF Corrupted? Database Repair Field Guide

SQL Server MDF Corrupted? Database Repair Field Guide

SQL Server MDF Corrupted? Database Repair Field Guide

Late last month, a logistics firm in Kwun Tong found their entire ERP wouldn't open in the morning: "Database 'LOGISTICS_DB' cannot be opened due to inaccessible files or insufficient memory." Their IT manager called us urgently: "Five years of shipping records inside, tax filing tomorrow — losing this database is a disaster." We remoted in: the MDF file was still there, but SQL Server marked it suspect — the classic symptom of database corruption. Here's what causes SQL Server corruption, how to repair it, and how to prevent recurrence.

Common Causes of SQL Server Corruption

  • Sudden power loss / server crash: Most common. SQL Server writes in strict order (write-ahead logging); a mid-write power cut leaves MDF and LDF inconsistent. On next boot, crash recovery runs — if the LDF is also damaged, the database gets marked suspect.
  • Disk bad sectors: MDF files are huge (GBs to hundreds of GB) spanning many sectors. A bad sector on a data page makes it unreadable. SQL Server reports errors 823, 824, or 825 (I/O errors).
  • RAID issues: Databases on RAID — if the array degraded or had issues during rebuild, written data may be incomplete. We've seen RAID 5 rebuild failures leave MDFs with mixed old and new data across pages.
  • Accidental deletion: Someone deletes the LDF thinking it's unimportant. Without the log file, SQL Server can't open the database — it needs the log for recovery. Actually easy to fix: reattach the MDF and rebuild the log.
  • Virus / ransomware: Ransomware doesn't just encrypt Office files — MDFs and BAKs get encrypted too. One client's entire SQL Server folder was encrypted, backups included.

Step 1: Assess the Damage

Don't hit repair immediately. Diagnose first:

  1. Check the SQL Server error log: In SQL Server Management Studio (SSMS), check the database state — SUSPECT, RECOVERY_PENDING, or OFFLINE. Each needs different handling.
    • SUSPECT: SQL Server can't open the database; usually MDF or LDF damage
    • RECOVERY_PENDING: SQL Server knows there's a problem but hasn't given up; may auto-repair
    • OFFLINE: Taken offline manually or by the system; may not indicate corruption
  2. Run DBCC CHECKDB: If the database still opens (or in single user mode): 
    DBCC CHECKDB ('LOGISTICS_DB') WITH NO_INFOMSGS;
    This checks page by page, reporting which pages are damaged, which tables affected, and severity.
  3. Check disk health: For I/O errors (823/824), check S.M.A.R.T. first. If the drive is dying, don't run CHECKDB on it — every read is torture. Copy the MDF/LDF to a healthy drive and diagnose there.
⚠️ Never do these:
• Don't delete the LDF hoping it'll "rebuild itself" — without the log, some damage is unrepairable
• Don't run DBCC CHECKDB REPAIR directly on the original — REPAIR_ALLOW_DATA_LOSS really does lose data; test on a copy first
• Don't restore an old backup over the same database name — it overwrites; the damaged version may still hold recoverable data

The Kwun Tong Case: Saving the ERP

The MDF was 280GB, LDF 15GB. CHECKDB found 47 damaged pages concentrated in two tables — the shipping records master and an index. Luckily, the core accounting voucher table was intact.

Our process:

  1. Full backup of current state: Copied the entire MDF and LDF to our machine. All subsequent work happened on the copy; originals sealed. Never skip this — if repair goes wrong, you can start over.
  2. Try EMERGENCY mode: 
    ALTER DATABASE LOGISTICS_DB SET EMERGENCY;
DBCC CHECKDB ('LOGISTICS_DB') WITH NO_INFOMSGS;
    EMERGENCY mode bypasses normal recovery, letting us read the MDF directly to assess damage depth.
  3. Export table by table: Don't repair the whole database — REPAIR_ALLOW_DATA_LOSS deletes damaged pages outright. Our approach: export each table with bcp or SELECT INTO; for damaged tables, try row by row, saving whatever reads. 
    bcp "SELECT * FROM [LOGISTICS_DB].[dbo].[ShippingRecords]" queryout "D:RecoverShippingRecords.bcp" -S localhost -T -n
  4. Rebuild into a new database: Created a fresh empty database, imported rescued tables one by one. The two damaged tables yielded ~93% of rows — losses were mainly three days of recent shipping records (written onto bad-sector locations).
  5. Reconcile: Compared rescued data against the client's paper records and Excel files, filling the three missing days. The client helped; we provided the gap list.

Two days, $12,000 HKD. The IT manager said, "Luckily the core accounting data survived — otherwise tax filing would've been impossible." We then set up daily automated backups (full + differential + log) and moved the MDF to a RAID 6 array.

What Does DBCC CHECKDB REPAIR Actually Do?

Many IT people have heard of REPAIR_ALLOW_DATA_LOSS without knowing what it does. Three levels:

  • REPAIR_FAST: Only fixes minor issues without data loss, e.g., inconsistent indexes. Deprecated, no longer used.
  • REPAIR_REBUILD: Rebuilds damaged indexes. No data loss, but only fixes index damage — not data page damage.
  • REPAIR_ALLOW_DATA_LOSS: The nuclear option. It directly removes damaged data pages from the database so it can come back online. The name says it — data will be lost. However many pages are damaged, that much data is gone.

My advice: REPAIR_ALLOW_DATA_LOSS is a last resort, and only ever on a copy, never the original. Before running it, tell the client exactly how much will be lost and get approval. Often, table-by-table export saves more — REPAIR deletes whole pages, but row-by-row export might read 8 of 10 rows on a damaged page.

Missing LDF — What Now?

Common problem. Someone accidentally deletes the .ldf, or it's too damaged to open. Rebuild it:

-- Set database to EMERGENCY first
ALTER DATABASE [YourDB] SET EMERGENCY;
-- Then single user mode
ALTER DATABASE [YourDB] SET SINGLE_USER;
-- Rebuild the log
DBCC CHECKDB ([YourDB], REPAIR_ALLOW_DATA_LOSS);
-- Remember to set back to multi user
ALTER DATABASE [YourDB] SET MULTI_USER;

Note: after rebuilding the log, un-checkpointed transactions are lost. If the database died during heavy writes, losses could be significant. That's why log backups matter — don't rely on full backups alone.

Preventing Recurrence

  1. Complete backup strategy: Not just full backups. Do: daily full (night) + differential every 4 hours + log backup every 15 minutes. Then even if the MDF dies, you lose at most 15 minutes.
  2. Verify backups: Having backups doesn't mean they're restorable. Test-restore one backup monthly. We've seen clients who backed up for three years, never tested, and discovered all backup files were corrupted when disaster struck.
  3. Put MDFs on reliable storage: RAID 6 or RAID 10 — never single disks or RAID 0. Database I/O is intense; drives die faster than on file servers.
  4. Enable CHECKSUM: SQL Server page checksums calculate a verification code on write and check on read. Bad sectors get caught early instead of silently rotting. 
    ALTER DATABASE [YourDB] SET PAGE_VERIFY CHECKSUM;
  5. Monitor drive health: SQL Server won't tell you a drive is dying. Use separate S.M.A.R.T. monitoring or simple alerting. Whenever we set up a database server, we always add drive monitoring.

Pricing Reference

  • Pure logical corruption (DBCC repairable): $4,000-$8,000 HKD, 1-2 days
  • Table-by-table export and rebuild: $8,000-$15,000, 2-4 days
  • Physical MDF damage (disk bad sectors): $12,000-$25,000, disk imaging first, 5-7 days
  • Ransomware encryption: depends; if backups are encrypted too, may be unrecoverable. Free assessment first.

FAQ

1. Q: My database shows SUSPECT. Will restarting SQL Server fix it?

A: No. SUSPECT means SQL Server already tried recovery and failed. Restarting just retries with the same result. Each restart risks making things worse (e.g., triggering auto-repair). Correct approach: stop, back up the MDF/LDF, then diagnose.

2. Q: I have an old backup. Can I just restore it?

A: Yes, but two caveats: first, restoring overwrites — the damaged version may still hold newer recoverable data, so rescue that first; second, restore to a new database name (e.g., LOGISTICS_DB_RESTORE), not over the original — swap only after verifying the data.

3. Q: Any repair differences between Express and Standard editions?

A: Same repair methods, but Express has a 10GB database limit. If the MDF exceeds 10GB, Express can't even attach it — you need Standard or Developer edition for recovery. We have all editions; not a concern.

4. Q: My MDF is 500GB. How long will recovery take?

A: Depends on damage extent and disk speed. Pure logical issues: CHECKDB alone may run hours (500GB page-by-page is slow). Table-by-table export: 2-4 days depending on table count. We'll do a preliminary check and give a firm time estimate.

Summary

SQL Server corruption isn't the end of the world, but order matters: back up current state → diagnose → repair on copies → verify table by table. Never run REPAIR on originals, never delete the LDF, never restore directly over the original. If your database is already suspect, stop and WhatsApp +852 6558 6806 — we have experience with hundred-GB databases, free initial assessment.

Learn more in our data backup services or computer repair services.

Found this article helpful?

Feel free to share it with your friends or colleagues.

Call Us