Discovering which pages are corrupt
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 ‘DBName’(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. Carrying out a page level SQL Server recovery
The Microsoft SQL Server page recovery operation is very similar to that of a full recovery, the only difference being the use of the NORECOVERY command:
RESTORE DATABASE DBName
PAGE = 'fileid:pageid' -- e.g. 1:254
FROM DISK = 'X:\SQLBackups\DBName_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. You can recover more than one page at a time simply by appending additional fileid:pageid combinations separated by commas.
You can test that the data has been recovered successfully by running DBCC CHECKDB on the affected database – it should complete without error.
Finishing up the page level SQL Server recovery
Although the corrupt page has been recovered successfully, you will also need to apply the transaction logs to ensure that all data is present and correct. For each transaction log generated since your last full backup, run the RESTORE LOG command:
RESTORE LOG DBName
FROM DISK = 'X:\SQLBackups\DBName_LogFileFrom2PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBName
FROM DISK = 'X:\SQLBackups\DBName_LogFileFrom215PM.TRN'
WITH NORECOVERY
GO Again, note the NORECOVERY option will ensure that you restore nothing but the specified log files. Finally you need to apply the tail log backup taken in the Prerequisites section above:
RESTORE LOG DBName
FROM DISK = 'X:\SQLBackups\DBName_TailEnd.trn
WITH RECOVERY This time you do want to recover the database, hence the WITH RECOVERY command.
At this point you can re-test the database to ensure that everything is working as it should. Once you are satisfied, switch the database back to multi-user mode to restore access for your users:
ALTER DATABASE AdventureWorks SET MULTI_USER
GO And there you have it, corruption problems resolved without having to undergo a full SQL Server recovery. The page level restore option ensures that downtime and user inconvenience is kept to a minimum, as are the costs associated with a database outage.
