How to repair a corrupted SQL Server database? Microsoft says to restore from the last known good backup. That is its primary method for any error that DBCC CHECKDB reports. First run DBCC CHECKDB without a repair option to see what is wrong. Use the REPAIR_ALLOW_DATA_LOSS option only as a last resort when no backup works, because Microsoft warns it can lose more data than a restore would.
Step 1: Confirm SQL Database Corruption
A database in the SUSPECT state is one sign of corruption. Microsoft's page on error 3414 says SQL Server puts a database in SUSPECT when it recovers but fails to start. You can see the state in SQL Server Management Studio, or in the state_desc column of sys.databases.
Step 2: Run DBCC CHECKDB Without a Repair Option
Microsoft's DBCC CHECKDB page says the command checks the logical and physical integrity of all objects in a database. You do not need to run CHECKALLOC, CHECKTABLE or CHECKCATALOG separately. A basic check looks like this:
DBCC CHECKDB (YourDatabase) WITH NO_INFOMSGS;
Microsoft also gives these facts:
- The command needs membership in the sysadmin fixed server role or the db_owner fixed database role.
- If you must use a repair option later, run the command without one first. The repair option to use is given at the end of the list of reported errors.
Step 3: Restore From a Backup
Microsoft states that it always recommends restoring from the last known good backup as the primary way to recover from errors that DBCC CHECKDB reports.
If only a few pages are damaged, a page restore may avoid restoring the whole database. Microsoft's page restore guide says:
- It repairs isolated damaged pages. If many pages in a file are damaged, restoring the whole file is generally more efficient.
- It needs the full or bulk-logged recovery model.
- It needs an unbroken chain of log backups applied after the last full or differential restore.
The damaged page IDs come from the suspect_pages table in msdb, the error log or DBCC output.
Step 4: DBCC CHECKDB Repair When You Have No Usable Backup
Microsoft describes the REPAIR options as an emergency measure. It says to use them only as a last resort, and that the database must be in single-user mode. Its warning says REPAIR_ALLOW_DATA_LOSS can cause more data loss than restoring from a good backup. The options differ:
- REPAIR_REBUILD: repairs with no possibility of data loss, such as rebuilding an index. It does not repair FILESTREAM errors.
- REPAIR_ALLOW_DATA_LOSS: tries to repair all reported errors, and deallocated data is no longer accessible or recoverable.
- REPAIR_FAST: kept only for backward compatibility. It performs no repair actions.
Microsoft's list of what to do before a repair:
- Run DBCC CHECKDB without a repair option to find the repair level.
- Restore from a backup if one exists.
- Back up the database before you use REPAIR_ALLOW_DATA_LOSS.
- Make physical copies of all database files, including .mdf, .ndf and .ldf files.
- Consider EMERGENCY mode and try to extract critical data first.
- Run the repair inside a transaction, so you can roll it back if you do not accept the result.
What Does Emergency Mode Do?
Microsoft says that in EMERGENCY mode the database is marked READ_ONLY, logging is disabled and access is limited to sysadmin members. DBCC CHECKDB can then use pages that were marked inaccessible. If the log is corrupt, it may be rebuilt, which Microsoft says might lose transactional consistency. The error 3414 page lists a salvage option: set the database to EMERGENCY with ALTER DATABASE, then copy as much data as possible to another database.
What to Do After a Repair
Microsoft says repair operations do not consider constraints between tables, and referential integrity may not be accurate after rows or pages are deallocated. After REPAIR_ALLOW_DATA_LOSS, run DBCC CHECKCONSTRAINTS. After a successful emergency repair, Microsoft says to run DBCC CHECKCONSTRAINTS and back up immediately. Logical consistency and business rules must be validated manually.
For more on damaged pages, see our guide on SQL Server page-level corruption. For a database stuck in that state, see how to fix a SQL Server suspect database.
What If SQL Server Reports Error 824?
Microsoft's MSSQLSERVER_824 page says error 824 means Windows read the page from disk successfully, but SQL Server found something wrong with it. The message calls it a severe error that threatens database integrity and says to run a full DBCC CHECKDB. Microsoft lists these steps:
- Query the suspect_pages table in msdb for other damaged pages.
- Run DBCC CHECKDB on the databases on the same volume.
- Turn on PAGE_VERIFY CHECKSUM if it is off.
- Check the Windows Event logs for storage or driver errors and fix those first.
- Work with the hardware vendor, and check filter drivers such as antivirus on the I/O path.
- If the cause is not hardware and a clean backup exists, restore from it.
Microsoft says error 824 usually points to a problem in the I/O subsystem, such as a failing disk, firmware or a faulty driver.
What Do These Sources Not Say?
- Microsoft does not say how often repairs succeed or how much data a repair typically loses.
- The restore overview page does not cover tail-log backups or give a full corruption workflow.
- The pages do not explain how to find the cause of corruption, such as storage hardware faults.
- They do not describe third-party repair tools. This guide covers Microsoft's own methods only.
- The error 824 page does not say how to recover rows from a page that SQL Server cannot read.
Frequently Asked Questions
How to repair a corrupted SQL Server database safely?
Microsoft says to restore from the last known good backup. It calls REPAIR_ALLOW_DATA_LOSS an emergency last resort for when a restore is not possible.
Is REPAIR_ALLOW_DATA_LOSS safe?
Microsoft warns it can cause more data loss than restoring from a good backup, and that deallocated data cannot be recovered.
Can I repair a database in multi-user mode?
No. Microsoft says the database must be in single-user mode to use a REPAIR option.
What should I do before a repair?
Back up the database, copy the database files, run the repair inside a transaction, and consider EMERGENCY mode to extract critical data first.
Key Takeaways
- How to repair a corrupted SQL Server database: restore from a known good backup first. Microsoft calls it the primary method.
- Run DBCC CHECKDB without a repair option to see the errors and the repair level.
- Use REPAIR_ALLOW_DATA_LOSS only as a last resort, in single-user mode, after a backup.
- Run DBCC CHECKCONSTRAINTS and back up straight after any repair.
Sources and References
Facts follow Microsoft Learn.
- DBCC CHECKDB (Transact-SQL) learn.microsoft.com
- MSSQLSERVER_3414 learn.microsoft.com
- MSSQLSERVER_824 learn.microsoft.com
- Restore Pages (SQL Server) learn.microsoft.com
- Restore and recovery overview (SQL Server) learn.microsoft.com