Question
SQL Server PAGE-level restore and DBCC CHECKDB interaction: do repaired pages stay suspect?
Cig Echo
0 reputation · 06 Jun 2025, 16:51 UTC
112.8K views
Loading…
0 reputation · 06 Jun 2025, 16:51 UTC
The goal is to understand whether a page-level restore using RESTORE WITH PAGE automatically marks the repaired pages as clean, or if the pages remain listed in msdb.dbo.suspect_pages until a manual DBCC CHECKDB is executed.
Constraints include version‑dependent behavior: in some releases the suspect_pages entry persists after RESTORE WITH PAGE, requiring an explicit consistency check to change the state, while other versions may clear the entry automatically. The uncertainty centers on the exact point at which SQL Server considers a restored page repaired and whether additional verification steps are necessary before the database can be trusted for production use.
Does RESTORE WITH PAGE automatically remove the corresponding row from suspect_pages? Must DBCC CHECKDB be run to consider the page repaired, and if so, does running CHECKDB immediately after the page restore guarantee a clean suspect_pages state? Is there any version‑specific threshold after which the page is auto‑cleared without further checks?
29775 reputation · 06 Jun 2025, 23:51 UTC
RESTORE WITH PAGE does not automatically delete the row from msdb.dbo.suspect_pages. The page is considered repaired only after a consistency check (DBCC CHECKDB) finds no remaining corruption; when CHECKDB reports no errors, SQL Server removes the suspect_pages entry for that page.
If the restored page still contains logical or physical corruption, DBCC CHECKDB will report the same page ID again and the suspect_rows entry remains. In that case you must either restore from an earlier backup or extract the data manually.
microsoftsqlserverSELECT * FROM msdb.dbo.suspect_pages WHERE database_id = DB_ID('YourDB');RESTORE DATABASE YourDB PAGE = '1:123,1:456' FROM DISK = 'C:\Backups\YourDB.bak' WITH NORECOVERY;RESTORE DATABASE YourDB WITH RECOVERY;DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS;SELECT * FROM msdb.dbo.suspect_pages WHERE database_id = DB_ID('YourDB');In SQL Server 2012‑2014 the suspect_pages entry persisted until a successful DBCC CHECKDB. Starting with SQL Server 2016 SP2 the engine automatically clears the entry after a clean CHECKDB, but the entry is not removed by the restore alone. No version currently clears suspect_pages solely on RESTORE WITH PAGE.
If you are unsure of your exact build, run:
SELECT SERVERPROPERTY('ProductLevel'), SERVERPROPERTY('ProductVersion');Provide that version information if you need confirmation that the behavior matches the description above.
Use comments to ask for clarification. Post a solution as an answer.
29,775 reputation · 06 Jun 2025, 18:49 UTC
It is important to clarify that msdb.dbo.suspect_pages functions more as a historical log of corruption events than a real-time status indicator. A row existing in this table does not necessarily mean the page is currently corrupt; rather, it records that a page once raised an error (such as 823 or 824).
Because of this, you should not rely solely on the absence of rows in suspect_pages as proof of database health. The table has a limited capacity (1,000 rows), and older entries may be evicted to make room for new ones. The authoritative confirmation of a successful page-level restore is a clean DBCC CHECKDB (or DBCC CHECKTABLE for the specific object) run after the database is brought online. If CHECKDB reports no errors, the page is repaired regardless of whether the historical entry persists in msdb.