SQL Server Database Corruption: Causes, Warning Signs and How to Recover Without Losing Data

“The query fails with error 824.” “The nightly backup stopped with a checksum error.” “CHECKDB returned a page full of red text.” Database corruption in SQL Server usually shows up in one of these ways, and the decisions made in the first hour often decide how much data you lose.

This article explains what SQL Server corruption is (and what it isn’t), what causes it, how to spot it early, and how to recover with the least possible data loss, depending on what was damaged.

What Is SQL Server Database Corruption?

SQL Server stores data in 8 KB pages inside data files. Corruption means one or more of these pages no longer contains what SQL Server wrote: the checksum doesn’t match, the page header is damaged, the page ID is wrong, or the links between pages are broken. Corruption can hit data pages, index pages, allocation pages, system metadata, the transaction log, or the backup files themselves.

It’s worth separating corruption from two things it’s often confused with:

• Accidentally deleted or changed data is not corruption. If someone ran a DELETE without a WHERE clause, the pages are perfectly healthy. The answer there is a point-in-time restore, not CHECKDB.
• A database in RECOVERY PENDING isn’t necessarily corrupt. A missing file, a full disk or a permissions problem can produce the same state. We cover that separately in SQL Server Database Stuck in RECOVERY PENDING or SUSPECT?

What Causes Corruption?

SQL Server itself very rarely writes a bad page. In the large majority of cases, the damage happens somewhere between SQL Server and the disk, in the I/O path:

• Disks and storage: failing drives, SAN or storage array firmware bugs, faulty controllers or cables.
• Drivers and filter drivers: outdated storage drivers, and software that sits in the I/O path, such as backup agents, encryption or antivirus filter drivers. Database files should be excluded from antivirus scanning.
• Power loss and write caching: a power cut while a write is in progress can leave a page half written (a “torn page”), especially when a disk or controller write cache isn’t protected by a battery or flash backup.
• Memory errors: a page can be damaged in memory before it’s written to disk.
• Unsafe file operations: copying or snapshotting database files while SQL Server is running, without a SQL Server-aware method.
• Software bugs: rare, but real in SQL Server, the operating system or storage software. That’s one reason to keep cumulative updates current.

Warning Signs: The Error Messages to Know

These messages appear in the SQL Server error log, which is the first place to look; see our SQL Server Error Log guide.

How to Detect Corruption Early

The earlier corruption is found, the more clean backups you have to choose from. Three mechanisms work together.

1. Check msdb.dbo.suspect_pages

SQL Server records every page that failed with an 823 or 824 error in this table, together with the error type:

SELECT DB_NAME(database_id) AS database_name,
       file_id,
       page_id,
       CASE event_type
            WHEN 1 THEN '823 / 824 (other)'
            WHEN 2 THEN 'Bad checksum'
            WHEN 3 THEN 'Torn page'
            WHEN 4 THEN 'Restored'
            WHEN 5 THEN 'Repaired by DBCC'
            WHEN 7 THEN 'Deallocated by DBCC'
       END AS event_desc,
       error_count,
       last_update_date
FROM msdb.dbo.suspect_pages
ORDER BY last_update_date DESC;

Rows with event types 1, 2 or 3 need attention. The table is limited to 1,000 rows, so once problems are resolved, clean up the old records.

2. Alerts for errors 823, 824 and 825

These errors only appear when a query touches the damaged page. Without an alert, the first person to notice may be a user. A SQL Server Agent alert and an email to the DBA team cost minutes to set up:

-- Requires Database Mail and an existing operator (here: 'DBA Team')
DECLARE @msg int, @name sysname;
DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT v FROM (VALUES (823), (824), (825)) AS t(v);
OPEN c; FETCH NEXT FROM c INTO @msg;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @name = N'Error ' + CAST(@msg AS nvarchar(10));
    EXEC msdb.dbo.sp_add_alert
         @name = @name, @message_id = @msg, @severity = 0,
         @delay_between_responses = 900, @include_event_description_in = 1;
    EXEC msdb.dbo.sp_add_notification
         @alert_name = @name, @operator_name = N'DBA Team', @notification_method = 1;
    FETCH NEXT FROM c INTO @msg;
END
CLOSE c; DEALLOCATE c;

3. Regular DBCC CHECKDB and page checksums

Alerts only fire for pages that are read. Rarely used tables and indexes can stay damaged for months. That’s why a regular DBCC CHECKDB is essential, and why the gap between two successful runs should be shorter than your backup retention. Also make sure every database uses PAGE_VERIFY CHECKSUM and that backups are taken WITH CHECKSUM. We cover frequency, options and production impact in detail in DBCC CHECKDB in SQL Server.

Corruption Found: The First Hour

Calm, ordered steps matter more than speed. What not to do:

• Don’t run REPAIR_ALLOW_DATA_LOSS as a first step. It can delete data, and Microsoft says it can lose more data than restoring from the last good backup.
• Don’t detach the database or restart SQL Server repeatedly “to see if it fixes itself”. A damaged database may not attach or recover again.
• Don’t overwrite or delete old backups. The backup from before the damage may be the one you need.

And what to do:

1. Collect the evidence: the error messages, the error log entries, the rows in suspect_pages and the time the problem was first seen.
2. Take a log backup if you can (full recovery model). It protects the work done since the last log backup and is the first step of any page or database restore.
3. Run DBCC CHECKDB to see the full extent: DBCC CHECKDB (N'SalesDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; Save the full output, including the “minimum repair level” line at the end.
4. Start looking at the storage: the Windows system log, storage and controller logs, and the hardware vendor’s diagnostic tools. If the disk is failing, it may be safer to restore to a different volume.

Choosing a Recovery Method: What Was Damaged?

The right recovery method depends on what the CHECKDB output says was damaged:

Page restore: repairing a few pages without restoring the whole database

If only a handful of pages are damaged and the database uses the full recovery model, you can restore just those pages from backup. All editions support it offline; Enterprise edition can do it while the database stays online. The file and page IDs come from suspect_pages, the error message or the CHECKDB output:

-- 1. Restore the damaged pages from the latest full backup
RESTORE DATABASE [SalesDB] PAGE = '1:57, 1:202'
FROM DISK = N'E:\Backup\SalesDB_FULL.bak'
WITH NORECOVERY;

-- 2. Apply the differential (if any) and every log backup since, in order
RESTORE LOG [SalesDB] FROM DISK = N'E:\Backup\SalesDB_LOG_01.trn' WITH NORECOVERY;
RESTORE LOG [SalesDB] FROM DISK = N'E:\Backup\SalesDB_LOG_02.trn' WITH NORECOVERY;

-- 3. Take a new log backup and restore it to bring the pages up to date
BACKUP LOG [SalesDB] TO DISK = N'E:\Backup\SalesDB_LOG_tail.trn';
RESTORE LOG [SalesDB] FROM DISK = N'E:\Backup\SalesDB_LOG_tail.trn' WITH RECOVERY;

-- 4. Confirm
DBCC CHECKDB (N'SalesDB') WITH NO_INFOMSGS;

Automatic page repair in Always On availability groups

In an availability group, when a replica can’t read a page because of error 823 (CRC failure), 824 or 829, it automatically requests a clean copy of the page from another replica and replaces it. No data is lost. File header pages, the boot page and allocation pages can’t be repaired this way. You can see recent attempts in sys.dm_hadr_auto_page_repair. Automatic repair fixes the symptom, not the cause, so a disk that keeps producing errors still needs attention. For more on availability groups, see What Is SQL Server Always On?

When there’s no backup: repair as a last resort

If no usable backup exists, DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS is the remaining option. Before you run it, copy every database file, put the database in single-user mode, run the repair inside a transaction so you can review the result, and afterwards run DBCC CHECKCONSTRAINTS and take a full backup. Repair may deallocate whole pages, and what was on them can’t be recovered.

After Recovery: Make Sure It Doesn’t Happen Again

• Find and fix the root cause together with the storage and system teams: replace hardware, update firmware and drivers, review antivirus exclusions.
• Run DBCC CHECKDB again and confirm the database is clean.
• Take a full backup immediately and test it by restoring it.
• Review the backup strategy: is retention long enough, and does CHECKDB run more often than backups expire? See How to Set Up the Best Backup Plan.
• Keep the alerts and the CHECKDB schedule in place, and monitor 825 errors in particular; they often appear before a disk fails.

Frequently Asked Questions