Page Restore

DBCC CHECKDB

To identify corruption in the database DBCC CHECKDB is used.

Finding corrupted pages

Usually the first sign that something is wrong will be an inconsistency error generated when a select statement is executed. The Microsoft SQL Server Messages pane will show an error like:

Msg 5242, Level 22, State 1, Line 1
An inconsistency was detected during an internal operation in database ‘SQLDoseDB’(ID:9) on page (1:254).
Please contact technical support. Reference number 4.
As you would expect, the page number referenced, 254, is the corrupt page that needs to be recovered.

Recovering corrupted pages

The Microsoft SQL Server page recovery operation is very similar to that of a full recovery, the only difference being the use of the NORECOVERYcommand:

RESTORE DATABASE SQLDoseDB
PAGE = 'fileid:pageid'  -- e.g. 1:254
FROM DISK = 'D:\SQLBackups\SQLDoseDB_lastFull.BAK'
WITH
NORECOVERY
GO

This will the re-insert the corrupted page from backup without going through the time-consuming process of a full SQL Server recovery. To recover more than one page at a time simply by add additional fileid:pageid combinations separated by commas.

Test that the data has been recovered successfully by running DBCC CHECKDB on the affected database – it should complete without error.