For day three of this six part series Introduction to Integrity, sponsored by Idera, we will take a look at my number two integrity issue: corruption in clustered indexes or heaps. Unlike corruption in allocation pages, we have several options for dealing with this type of corruption. This particular corruption issue does require a lot more care than many of the others types we will look at in this series.

If the corruption occurs in index ID of 0 or 1, then you are dealing with a heap or a clustered index. One of the key things to keep in mind here is that a heap or clustered index is the base underlying data. You cannot fix corruption here by rebuilding or recreating anything. You will likely hear recommendations to drop and recreate the clustered index or rebuild the clustered index or table. These actions cannot make missing data magically reappear.

Corruption in Clustered Indexes and Heaps

If you run DBCC CHECKDB on the sample corrupt database CorruptDB (Demo_ClusteredIndexCorruption.zip (5.03 MB)), you will receive a message similar to the one below:

Object ID 245575913, index ID 1 … Page (1:298) could not be processed. See other errors for details.

The key pieces of information here is the Object ID (resolves to dbo.FactInternetSales), index ID of 1 (clustered index), and page 1:298. It is a single page of a clustered index.

If the corruption is in a heap or clustered index, the proper way to fix it is through restore. If the corruption is not too wide-spread, single-page restores is the quickest way to get a large database fully back online. If the corruption is wide-spread, it may be quicker to restore the full database than to perform a series of single-page restores. At that point, it’s a judgment call.

If you decide to try a single-page restore, the first thing you should do is to verify your backups. You will need a full or differential backup with a good version of the page in it, and you will need all transaction log backups, including the current tail of the log, since the full or differential backup to bring the page current with the rest of the database. The transaction log backups are absolutely required. Do not even try a single page restores without all of the required backups.

We identified above in the sample corrupt database that it is a single-page of a clustered index. After verifying that I have proper backups to support a single-page restore, I start the restore process. The steps I follow are:

1. Put the database in RESTRICTED_USER mode
2. Restore the specific page from a full or differential backup
3. Restore all transaction log files since the full or differential backup using NORECOVERY
4. Back up the tail of the log file
5. Restore the tail of the log with NORECOVERY
6. Recover the database
7. Run DBCC CHECKDB to ensure corruption is gone
8. Set the database back to MULTI_USER

If you are new to performing restores, this may seem daunting the first few times because you the restore wizard does not support the options you need. You must perform the restores using T-SQL. The SQL commands are actually very simple. The initial restore of the page from the full or differential backup requires specifying which page to restore. The transaction log backups are very simple because you don’t have to specify any special settings other than NORECOVERY.

Let’s walk through the restore process for the sample corrupt database:

-- Only the one page is corrupt, so let's do a page restore
-- Switch to master to restore the damaged page
USE master;
Go

-- Set the database in restricted user mode to keep average users out
Alter Database CorruptDB Set Restricted_User With Rollback Immediate;
Go

-- Restore the corrupt page from the good full backup
Restore Database CorruptDB
    Page = '1:298'
    From Disk = '<Path to full backup>CorruptDB.bak';
Go

-- Restore the 1st pre=existing log backup to bring the page current
-- SQL knows which transations to apply, no need to specify any special commands
Restore Log CorruptDB
    From Disk = '<Path to log backups>CorruptDB.trn'
    With NoRecovery;
Go
-- If there were more pre-existing log backups, we would restore them in order

-- Now backup the tail of the log...
Backup Log CorruptDB
    To Disk = '<Path to tail log backup>CorruptDB_LOG_TAIL.trn'
    With init;
Go

-- Restore the tail of the log bringing the page current
Restore Log CorruptDB
    From Disk = '<Path to tail log backup>CorruptDB_LOG_TAIL.trn'
    With NoRecovery;
Go

-- Finally, recover the database to bring it online
Restore Database CorruptDB With Recovery;
Go

-- Recheck the database for corruption again
DBCC CheckDB(CorruptDB) With All_ErrorMsgs, No_InfoMsgs, TableResults;
Go

-- Allow users back in
Alter Database CorruptDB Set Multi_User;
Go

If CHECKDB reports that there is no more corruption, then you know you have been successful. You quickly recovered corrupt data with no data loss and minimal downtime. If it still reports that the corruption exists or if new corruption is found, it’s time to consider doing a full restore instead of a single-page restore.