查找损坏页（suspect_pages）并还原单个页

命令

-- Pages that hit 823/824 or checksum errors:
SELECT DB_NAME(sp.database_id) AS db, sp.file_id, sp.page_id,
       CASE sp.event_type WHEN 1 THEN '823/824 error' WHEN 2 THEN 'bad checksum'
            WHEN 3 THEN 'torn page'     WHEN 4 THEN 'restored'
            WHEN 5 THEN 'repaired'      WHEN 7 THEN 'deallocated by DBCC' END AS event_desc,
       sp.error_count, sp.last_update_date
FROM   msdb.dbo.suspect_pages sp
ORDER BY sp.last_update_date DESC;

-- Page restore (example 1:336): FULL + later logs + a new log backup
RESTORE DATABASE [Musteri] PAGE = '1:336'
FROM DISK = N'D:\Backup\Musteri_full_20261008.bak' WITH NORECOVERY;
RESTORE LOG [Musteri] FROM DISK = N'D:\Backup\Musteri_log_1200.trn' WITH NORECOVERY;
BACKUP LOG [Musteri] TO DISK = N'D:\Backup\Musteri_log_pagetail.trn' WITH INIT;
RESTORE LOG [Musteri] FROM DISK = N'D:\Backup\Musteri_log_pagetail.trn' WITH RECOVERY;

-- Verify:
DBCC CHECKDB (N'Musteri') WITH NO_INFOMSGS, ALL_ERRORMSGS;

作用

从 msdb.dbo.suspect_pages 列出遇到 I/O（823/824）、校验和或页撕裂错误的页；从 FULL 备份中取出各个损坏页并用日志前滚进行修复，无需还原整个数据库。

适用场景

错误日志中出现 823/824 错误、CHECKDB 中的一致性错误仅限于少数几页，或用户查询因“incorrect checksum”失败时。

注意

需要 FULL 或 BULK_LOGGED 恢复模式以及完整的日志链。联机页还原仅限 Enterprise/Developer；其他版本在还原期间数据库处于脱机状态。分配页（GAM/SGAM/PFS）、文件头和引导页（1:9）无法用此方式还原。823/824 通常意味着存储问题；未检查磁盘/控制器/SAN 就不要结案，否则会再次发生。坏页很多时，完整还原数据库更快。