SQL Server database corruption means damaged pages in your MDF file and Microsoft says to fix the hardware before any repair. Five methods follow and they are a hardware fix with DBCC CHECKDB, a backup restore, REPAIR_REBUILD, REPAIR_ALLOW_DATA_LOSS and recovery software. Microsoft calls the data loss repair an emergency last resort. Copy the database files to a safe folder first and test recovery software on a spare MDF copy because the demo exports only 25 records per table.
SQL Server database corruption is damage to the pages that store your data. Microsoft explains that the Database Engine moves data in pages of 8 KiB so one damaged page affects every row stored on it.
Microsoft guidance points to the disk system and the I/O path first so the fix of SQL Server database corruption starts outside the database. Backups decide how much data you recover.
Causes, early detection and pre checks come first and the five fixes follow with a prevention section at the end.
What is SQL Server Database Corruption?
SQL Server database corruption means a page or a structure fails a check. SQL Server reports the failure as error 823, 824 or 825 or as a consistency error from DBCC CHECKDB.
Error 824 is the logical consistency error that damaged pages trigger.
Four signals report SQL Server database corruption:
| Signal | Meaning |
|---|---|
| Error 823 | Windows detected that an I/O request failed |
| Error 824 | Windows reports a successful I/O request but SQL Server finds a problem with the page |
| Error 825 | An I/O request needed retries before it succeeded |
| CHECKDB errors | DBCC CHECKDB found allocation or consistency errors |
Microsoft notes that eight contiguous 8 KiB pages form a 64 KiB extent. Our Error 825 Fix Guide explains why error 825 needs attention even after the request succeeds.
What Causes SQL Server Database Corruption?
Microsoft says that the cause of database corruption ranges from file system corruption and hardware issues to driver issues and corrupted pages in memory or storage cache. Six causes appear in Microsoft guidance:
- Disk and Hardware Failures. Per Microsoft the most frequent reasons for disk I/O path errors are power failures and disk hardware failures at the moment a page is saved to disk.
- Power Failures During a Page Save. A torn page appears when only part of an 8 KiB page reaches the disk.
- Unsafe Caching. Microsoft notes that unsafe caching can corrupt databases regardless of the consistency of the transaction log and that an uninterruptible power supply alone is generally not sufficient.
- File System and Driver Faults. A damaged file system or a faulty driver returns wrong data to SQL Server.
- Filter Drivers and Antivirus Scans. Microsoft lists filter drivers like virus software among the components to investigate after a checksum failure.
- Problems in SQL Server. Microsoft includes problems with the SQL Server software in the same group of causes.
How to Detect SQL Server Database Corruption Early?
Five checks find database corruption before users report it:
- Error Log Entries. Error 824 appears in the SQL Server error log and in the Windows event log.
- Suspect Pages. SQL Server lists suspected pages in msdb. Our Suspect Pages Monitoring Guide shows how to watch that table.
- DBCC CHECKDB. Microsoft recommends the PHYSICAL_ONLY option for frequent checks on production systems and a full check at regular intervals.
- Backup Checksums. A backup with a checksum verifies the page checksum and fails when it meets a page error.
- Page Verification. The CHECKSUM option discovers damaged pages when SQL Server opens them.
Use the statement below for the light daily check.
DBCC CHECKDB (N'LibraryDB') WITH PHYSICAL_ONLY;
What Needs a Check Before You Fix Database Corruption?
Confirm six things before any repair begins:
- Hardware. Microsoft advises you to resolve hardware problems before you restore a backup or repair the database.
- Error List. DBCC CHECKDB prints the damaged objects and the lowest repair level so store every line of the output.
- Backups. Locate the latest good full backup together with all log backups made after it.
- Recovery Model. Confirm full or bulk logged mode before you plan a page restore.
- SQL Server Version. According to Microsoft a backup cannot be restored to an older SQL Server version than the one that created it.
- File Copies. Duplicates of every data and log file belong on a separate volume.
Microsoft calls the data loss repair option an emergency last resort and adds that it is no alternative to a restore from a good backup.
5 Methods to Fix a Corrupt SQL Server Database
Five methods fix database corruption and four of them are free. Method #1 finds the cause while methods #2 to #5 repair the database in the order of risk.
| Feature | Hardware and CHECKDB | Backup Restore | Rebuild Repair | Data Loss Repair | Recovery Utility |
|---|---|---|---|---|---|
| Charge | None | None | None | None | License after a demo capped at 25 records per table |
| Fixes | The cause and the error list | Damaged pages up to a full database | Index and structure errors | Errors without a backup | Objects of a damaged file |
| Needs | Admin rights and event logs | A good backup | Single user mode | Single user mode | Spare MDF file duplicate |
| Main risk | Skipped hardware faults return | Data after the backup is lost | Errors that need a higher level remain | More data loss than a restore | License cost after the demo |
Server 2008, 2012 R2 and 2016 are on the list along with Windows 7, 8, 10 and 11. Other needs include 100 MB of free storage and 4 GB of RAM plus .NET Framework 4.5 or above.
Method #1: Fix the Hardware and Confirm With DBCC CHECKDB (Free, Start Here)
Microsoft requires that hardware problems are resolved before you restore a backup or repair a database. Repairs before the hardware fix do not remove the cause.
- Open Event Viewer and check the System log for disk, controller and driver errors.
- Update the drivers and the firmware that the log names.
- Disable the disk cache for the data and log volumes unless the storage maker confirms a battery backed cache or persistent flash storage.
- Add the data files, the log files and the backup files to your antivirus exclusion list.
- Execute the statement below and save the full output.
DBCC CHECKDB (N'LibraryDB');
Microsoft explains in its DBCC CHECKDB troubleshooting guide that a checksum error shows damage after SQL Server saved the page so the I/O subsystem needs a thorough check.
Note. Microsoft names the antivirus files as .mdf, .ldf, .ndf, .bak and .trn and the sqlservr.exe process.
Method #2: Restore From the Last Good Backup (Free, Microsoft Preferred)
Microsoft names a restore from a good backup as the best solution for permanent consistency errors. Restoring fixes SQL Server database corruption in the full database or only in damaged files or pages.
- Select the last full backup that passed a CHECKSUM verification.
- Execute the statements below. Replace the names and paths with the values of your system.
RESTORE DATABASE LibraryDB
FROM DISK = N'F:\SQLBackups\LibraryDB_full.bak'
WITH NORECOVERY, REPLACE, STATS = 10;
RESTORE LOG LibraryDB
FROM DISK = N'F:\SQLBackups\LibraryDB_log1.trn'
WITH NORECOVERY;
RESTORE DATABASE LibraryDB WITH RECOVERY;
- Apply every log backup in order before the last statement.
- Execute DBCC CHECKDB on the restored database.
Per Microsoft RESTORE VERIFYONLY checks headers and checksums but not the data structure of the backup so only a CHECKDB check proves the result. Restoring single pages swaps only the broken ones and our Error 824 Restore Steps list the procedure.
Note. A database restore takes the database offline according to Microsoft while online file and page restores need Enterprise edition.
Method #3: Repair With REPAIR_REBUILD (Free, No Data Loss)
REPAIR_REBUILD handles SQL Server database corruption in indexes without any possibility of data loss according to Microsoft. Repairs range from missing rows in nonclustered indexes to a rebuilt index.
- Copy the database files first.
- Set the database to single user mode.
- Execute the repair and then return the database to multi user mode.
ALTER DATABASE LibraryDB SET SINGLE_USER
WITH ROLLBACK AFTER 30 SECONDS;
GO
DBCC CHECKDB (N'LibraryDB', REPAIR_REBUILD);
ALTER DATABASE LibraryDB SET MULTI_USER;
Microsoft adds that the minimum repair level does not mean the option fixes every error. Execute DBCC CHECKDB again and compare the result with the first output.
Method #4: Repair With REPAIR_ALLOW_DATA_LOSS (Free, Final Option)
Microsoft states that REPAIR_ALLOW_DATA_LOSS attempts every repair for the reported errors and that some of these repairs cause data loss. Restoring your best backup can save more data than this option.
- Confirm that no usable backup exists.
- Copy the database files.
- Start single user mode first.
- Execute the statement below and review the output.
DBCC CHECKDB (N'LibraryDB', REPAIR_ALLOW_DATA_LOSS);
Microsoft warns that the repair can end with a logically inconsistent database and notes that not every repair loses data. Compare the row counts of important tables with your last backup afterwards. If the repair fails, our DBCC Repair Troubleshooting Guide lists the next actions.
Method #5: Export a Corrupt MDF File With EmailDoctor (Demo First, Then License)
Recovery software suits the case where no usable backup exists and the repairs fail or cost too much data. EmailDoctor SQL Database Recovery opens a damaged MDF file together with its NDF files and displays the tables, views and other objects it recovers before any export.
Work on a spare copy so the original file remains untouched. Demo exports save a limited set of records and objects.
Scan the Corrupt MDF Copy
- Start the software and wait for the main window.
- Click Open on the toolbar and go to the saved copy of LibraryDB.mdf.
- Click Quick Scan. Version detection is automatic when Auto detect is ticked and a cleared box opens the manual list.
- Add secondary NDF files of the database from the NDF Options tab.
- Review the scan report and confirm the Quick Scan note with OK.
- Browse the tree on the left and open any table to see the recovered records.
Move the Recovered Data to a Fresh Database
- Open Export in the menu bar and use Export As SQL Server Database.
- Type the SQL Server instance name. Use Windows Authentication for your current sign in. SQL Server Authentication needs a SQL login with its password.
- Go for Create New Database and enter LibraryDB_Recovered as its name.
- Mark the database components and set With Schema & Data.
- Press Export and wait for Export Completed. Close the message with OK.
- Execute DBCC CHECKDB on LibraryDB_Recovered through SSMS and take a fresh full backup.
Too few tables in the Quick Scan tree call for Advanced Scan next. Compare both modes in the MDF repair walkthrough.
How to Prevent SQL Server Database Corruption?
Six habits lower the risk of database corruption:
- CHECKSUM Page Verification. The default is CHECKSUM but an upgrade retains the old NONE or TORN_PAGE_DETECTION value according to Microsoft so update it yourself.
- Backups With Checksums. Checksum backups verify the page checksum and fail on a page error according to Microsoft.
- Regular CHECKDB. Execute the PHYSICAL_ONLY check frequently and the full check at regular intervals.
- Safe Caching. Microsoft advises you to disable the disk cache unless it is battery backed or uses persistent flash storage.
- Antivirus Exclusions. Exclude the data, log and backup files and the sqlservr.exe process.
- Test Restores. Restore a backup on a test server and execute DBCC CHECKDB because RESTORE VERIFYONLY checks only headers and checksums.
Use the first statement below to set the page verification and the second one to take a checksum backup.
ALTER DATABASE LibraryDB SET PAGE_VERIFY CHECKSUM;
BACKUP DATABASE LibraryDB
TO DISK = N'E:\Backup\LibraryDB_full.bak'
WITH CHECKSUM;
Microsoft states in the ALTER DATABASE SET options reference that PAGE_VERIFY can be set without taking the database offline.
Troubleshooting Common SQL Server Database Corruption Problems
Six problems delay the fix of database corruption:
- Errors Return After the Repair. The hardware fault is still active so repeat Method #1.
- Checksum Errors Appear Again. A checksum error means the damage happened after SQL Server saved the page according to Microsoft so check the I/O subsystem.
- Repair Option Refuses to Start. Microsoft requires single user mode so switch the database first.
- Backup Fails With a Page Error. The checksum check found a damaged page so restore the pages or use Method #5 on a copy.
- Restore Fails on Another Server. The destination instance uses an older SQL Server version than the backup source.
- Errors Come and Go. Microsoft links intermittent errors to disk cache issues in a device driver or another part of the I/O path so check the cache settings.
Frequently Asked Questions
Is Hardware the Only Cause of Database Corruption?
No. Microsoft lists the file system, drivers, caches and SQL Server problems next to hardware faults. Check the hardware first because Microsoft requires it before a restore or repair.
Can a Power Failure Cause Database Corruption?
Yes. Microsoft ranks power failures during a page save among the most frequent reasons. Microsoft adds that an uninterruptible power supply alone is generally not sufficient.
Does DBCC CHECKDB Fix Database Corruption?
No. DBCC CHECKDB checks the database and repairs only when you add a REPAIR option. Microsoft prefers restoring a known good backup to any repair.
Is the Data Loss Repair Option Safe?
No. Microsoft calls the option an emergency last resort and notes that a restore can save more data than the repair. Copy the files and execute the option only when no backup exists.
Does CHECKSUM Page Verification Prevent Corruption?
No. CHECKSUM detects a damaged page when SQL Server opens it and reports error 824. Prevention needs safe hardware and backups.
Can a Backup Contain Database Corruption?
Yes. Microsoft notes that a backup includes pages without checksum or torn page data as they are because the pages cannot be verified. Take backups WITH CHECKSUM so a page error stops the backup.
Does RESTORE VERIFYONLY Prove That a Backup is Clean?
No. Microsoft notes that RESTORE VERIFYONLY checks headers and checksums but not the data structure of the backup. Restore the backup on a test server and execute DBCC CHECKDB.
Can Antivirus Software Damage a SQL Server Database?
Yes. Per Microsoft a virus sweep that opens a database file during startup can mark the database as suspect and filter drivers like virus software are among the components to investigate. Exclude the database files from scans.
Can Recovery Software Fix Database Corruption in the MDF File?
No. Recovery software exports the readable objects into a new database and does not touch the damaged original. Choose it only when no backup exists to restore from.
Is the Free Demo Limited to 25 Records?
Yes. Expect a cap of 25 records per table and 5 more objects in the demo while a paid license removes the cap. Check the output on a spare MDF copy first and buy a license afterwards.
Author’s Verdict
Microsoft guidance points to the I/O path first so fix the hardware before you touch the database. Database corruption returns when the cause remains. Execute DBCC CHECKDB to learn the scale of the damage.
Restore from a good backup whenever one exists. Use REPAIR_REBUILD for index errors and reserve the data loss option for the case without a backup.
EmailDoctor recovery software copies readable tables and views from the corrupt file into a new database. Enable CHECKSUM page verification and take backups with checksums to detect damage early.
- SQL Server database corruption comes from hardware, power, cache, driver and antivirus problems.
- Fix the hardware first and restore the last good backup.
- REPAIR_REBUILD loses no data while REPAIR_ALLOW_DATA_LOSS is the last resort.
- Recovery software saves readable tables and views from the corrupt file in a fresh database.
- CHECKSUM page verification and backup checksums detect damage early.
Next Read: Repair a SQL Server Database
Also Read: Integrity Check Failed Fix | Suspect Pages Monitoring