Sql-server – How to check a database for a specific page

corruptiondbcc-checkdbsql server

I am working on repairing a database, I have about 80 errors that I need to fix and there seem to be three types of problems that I don't know how to fix other than doing a REPAIR_WITH_DATA_LOSS.

I do have a previous backup which does not have issues, and I have read about doing page level restores. However in my attempts to do that so far I have been unsuccessful using SSMS and TSQL. Is it possible to query the "good" database to see if it contains these pages? For example can I run something to look for page "1:10429855"?

Also is there a way to see what that same page is associated to or is on the "bad" database?

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID
-4342159331760996352 (type Unknown), page (31205:-867600381). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -4.

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID
6691734499630645248 (type Unknown), page (23723:203812287). Test
(IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -11.

Table error: Object ID 1518653945, index ID 1, partition ID
72057594166444032, alloc unit ID 71875645566156800 (type LOB data).
The off-row data node at page (1:10429953), slot 0, text ID
954357841920 is referenced by page (1:10429855), slot 4, but was not
seen in the scan.

Object ID 1518653945, index ID 1, partition ID 72057594166444032,
alloc unit ID 72057594253410304 (type In-row data): Errors found in
off-row data with ID 954358824960 owned by data record identified by
RID = (1:10488687:10)

Best Answer

To get information about a particular page in SQL server, you'll need to use the undocumented DBCC PAGE command. Or if you are using SQL Server 2012 and higher, you can also query the sys.dm_db_database_page_allocations DMV.

Regarding problems with page restores, unless you are on Enterprise Edition, page restores have to be performed OFFLINE. (i.e. you must not RECOVER the database and perform the log restore or full restore with the NORECOVERY option which will leave the database in a state that allows page restores in either edition)

Here's a quick blog post I found that introduces the sys.dm_db_database_page_allocations DMV: SQL SERVER 2012 – sys.dm_db_database_page_allocations – dynamic management function

For a quick overview of the DBCC PAGE command, you can read Paul Randal's blog post about it: How to use DBCC PAGE

And an introduction for how to do page restores in Enterprise and Standard edition: How to Restore a Page in SQL Server Standard and Enterprise Edition

Related Question