Repairing a SQL Server database starts with the safest path: restore a clean backup if you have one. If not, diagnose corruption with DBCC CHECKDB, use REPAIR_REBUILD first, and treat REPAIR_ALLOW_DATA_LOSS as a last resort. This guide lays out the right order for ONLINE, SUSPECT, and RECOVERY_PENDING cases.
Applies to: SQL Server 2012-2025 (Windows/Linux). Azure SQL Database uses built-in automated backups and point-in-time restore (see note below).
Quick Answer (2026)
Best practice: restore from a clean backup if possible; use DBCC repair options only as a last resort.
| What you see | Best first move | Why |
|---|---|---|
| DB is ONLINE, but errors show up / CHECKDB reports corruption | Run DBCC CHECKDB (diagnose) → REPAIR_REBUILD if needed | REPAIR_REBUILD is the no-data-loss repair option. |
| DB is SUSPECT / RECOVERY PENDING | Restore clean backup first; avoid emergency repair first | State can be caused by I/O or log issues; restore is safest. |
| No good backup exists | Salvage plan: copy files → test repairs on a copy → export what you can to a new DB | Microsoft recommends schema-out + export/import when repair is not clean. |
60-second triage (read-only)
Run these read-only checks before any repair.
Current state (all platforms):
__PREBLOCK_0__Last backups (SQL Server):
__PREBLOCK_0__Backup history (Azure SQL Database):
__PREBLOCK_0__\nNote: Requires VIEW DATABASE STATE.
\nSuspect pages (SQL Server):
__PREBLOCK_0__Before you touch DBCC
- Fix underlying storage/I/O issues first (or corruption can recur).
- Make physical copies of MDF/NDF/LDF before any repair.
- Prefer restore from a known good backup when possible.
Why SQL Server databases become corrupt?
Corruption is usually caused by storage/I/O faults, abrupt shutdowns, or software issues. Common causes include:
- Storage or controller failures.
- Sudden shutdowns or power loss.
- Software/driver bugs.
- Disk space exhaustion or file system issues.
- Human error (manual deletes or misconfiguration).
Signs of a corrupt SQL Server database
Look for these indicators before attempting repair:
- CHECKDB or checksum errors.
- Queries failing or missing data.
- Backup failures or I/O error messages.
- Database stuck in SUSPECT or RECOVERY_PENDING.
- Repeated crashes or log errors.
Common SQL Server corruption errors
| Error | What it usually means | First action |
|---|---|---|
| 823/824/825 | I/O or logical consistency errors | Check storage/event logs, stabilize I/O, then run CHECKDB. |
| Checksum errors | Data page corruption | Restore if possible or follow CHECKDB guidance after I/O is stable. |
| Torn page | Incomplete write, often power loss | Restore or run CHECKDB after fixing the root cause. |
| CHECKDB consistency errors | Logical corruption | Follow minimum repair level or salvage to a new database. |
How to repair a SQL Server database (safe order)
Start with restore when possible. Use DBCC CHECKDB to diagnose, then REPAIR_REBUILD if it is recommended. Treat REPAIR_ALLOW_DATA_LOSS as a last resort when no backup exists.
Method 0: Restore from a clean backup (recommended)
- Fix underlying I/O or storage issues first.
- Restore the most recent clean backup.
- Run DBCC CHECKDB to confirm the restored database is consistent.
Method 0.5: Page restore (when only a few pages are corrupt)
If only a small set of pages is damaged and you have a good backup chain, page restore can be the least destructive path. Offline page restore works in all editions; online page restore requires Enterprise.
- Identify suspect pages (msdb.dbo.suspect_pages).
- Restore the damaged pages from backup and roll forward with log backups.
- Run CHECKDB to confirm consistency.
Azure SQL Database note
Page restore is not used the same way in Azure SQL Database. Use built-in point-in-time restore and Microsoft support if integrity issues are suspected.
Azure SQL Database: restore-first path
Azure SQL Database does not support EMERGENCY or SINGLE_USER. Use point-in-time restore or geo-restore, and contact Microsoft support if integrity issues persist.
Method 1: DBCC CHECKDB (no-data-loss path first)
DBCC CHECKDB is the primary integrity check. Run it first to diagnose and follow the minimum repair level it recommends.
Run this first:
__PREBLOCK_0__- Make physical copies of MDF/NDF/LDF before any repair.
- Run CHECKDB without repair first, then decide.
- REPAIR_FAST is backward compatibility only; it performs no repairs.
Repair options (what they do):
- REPAIR_REBUILD: no data loss; rebuilds indexes and fixes minor issues.
- REPAIR_ALLOW_DATA_LOSS: last resort; can delete corrupted data to make the database consistent.
What CHECKDB’s recommendation really means
CHECKDB reports the minimum repair level it believes could make the database consistent. This is not a guarantee. Restore from a known good backup is preferred; use REPAIR options only when restore isn’t possible. When repair is not clean, schema-out + export can be safer than REPAIR_ALLOW_DATA_LOSS.
If repair is required:
__PREBLOCK_0__If CHECKDB recommends REPAIR_ALLOW_DATA_LOSS and you have no clean backup, expect data loss and validate carefully.
After repair: run DBCC CHECKCONSTRAINTS, validate key business queries, and take a full backup. Repairs do not maintain constraints or relationships automatically; manual validation is required. In normal mode, you can wrap repairs in a user transaction to allow rollback; in EMERGENCY mode repairs run outside user transactions.
CHECKDB performance notes (large databases)
- Use ESTIMATEONLY to estimate tempdb usage before a full check.
- Run PHYSICAL_ONLY more often for low-overhead checks; run full CHECKDB periodically.
- Use MAXDOP to control parallelism on busy servers.
- TABLOCK can be faster but reduces concurrency and limits some checks.
ONLINE vs TABLOCK
By default, CHECKDB uses an internal snapshot for consistency (ONLINE). TABLOCK can be faster under load but reduces concurrency and can skip some checks (for example, CHECKCATALOG and Service Broker validation).
Method 2: Fix SQL Server database in SUSPECT / RECOVERY PENDING (do this in order)
Check the SQL Server error log first. SUSPECT/RECOVERY_PENDING can be caused by missing or locked LDF files, file-path permission issues, log full conditions, or transient storage failures.
- Check the SQL Server error log for the first failure reason and resolve underlying I/O or storage issues.
- Verify the log file exists and that the SQL Server service account has permissions.
- Restore a known good backup (preferred).
- If no backup exists, set EMERGENCY and SINGLE_USER and run CHECKDB with REPAIR_ALLOW_DATA_LOSS on a copy.
Emergency mode guardrails
- Run on a file copy first.
- Fix I/O root cause first or corruption may return.
- Expect data loss; validate constraints and key totals afterward.
- Repairs in EMERGENCY mode run outside user transactions and cannot be rolled back.
Warning
Do not jump straight to EMERGENCY + REPAIR_ALLOW_DATA_LOSS unless you accept data loss and have no clean backup. Test on copies first.
Method 3: Salvage data to a new database (when repair is not safe)
If CHECKDB cannot repair cleanly, salvage what you can into a new database.
- Script schema (tables, indexes, constraints) to a new database.
- Export data from healthy tables (BCP/SSIS) and skip broken ones.
- Rebuild indexes and validate relationships.
Method 4: SysCurve SQL Recovery Tool (when restore/DBCC is not possible)
Use a recovery tool when DBCC fails, the database cannot come online, or you must recover deleted records before repair.
- Scans MDF/NDF and previews recoverable objects.
- Exports to a new database or a live SQL Server instance.
- Helps preserve data when REPAIR_ALLOW_DATA_LOSS is not acceptable.
Prevention + root cause checklist
- Fix underlying I/O or hardware issues before repair, or errors will return.
- Schedule DBCC CHECKDB regularly; use PHYSICAL_ONLY for lower overhead and full CHECKDB periodically.
- Validate backups with restore tests and document the last known good backup.
Sources (Microsoft)
- DBCC CHECKDB (warnings and repair guidance)
- Restore and recovery overview
- Repairing corrupted databases
- MSSQLSERVER 823 error
- MSSQLSERVER 824 error
Frequently Asked Questions
What is the safest way to repair a SQL Server database?
Restore from a clean backup. Microsoft recommends restore over repair whenever possible.
Is REPAIR_FAST still used?
No. REPAIR_FAST is a backward-compatibility option and performs no repair actions.
What is the difference between REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS?
REPAIR_REBUILD is the no-data-loss option and fixes minor issues like index rebuilds. REPAIR_ALLOW_DATA_LOSS is a last resort that can delete corrupted data to make the database consistent.
Why does CHECKDB recommend ALLOW_DATA_LOSS?
CHECKDB reports the minimum repair level it believes can make the database consistent. It does not guarantee no loss, so restore is safer if you have a clean backup.
Can I repair SUSPECT or RECOVERY PENDING without a backup?
You can try EMERGENCY + CHECKDB repair on a copy, but data loss is likely. Salvaging data to a new database is often safer than repeated repairs.
What should I do after a repair?
Run DBCC CHECKCONSTRAINTS, validate key business queries, and take a full backup immediately.
Can a recovery tool help with deleted records?
Yes. Use a recovery tool on a copy before running repairs if recovering deleted records matters.
The Final Word
Start with a clean restore whenever possible, and use DBCC repair options only as a last resort. Diagnose first, fix the root cause, and validate after every repair. For severe corruption or deleted-record recovery, use a recovery tool on a copy. The safest outcome is a consistent database with verified backups.
