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:
- Put the database in RESTRICTED_USER mode
- Restore the specific page from a full or differential backup
- Restore all transaction log files since the full or differential backup using NORECOVERY
- Back up the tail of the log file
- Restore the tail of the log with NORECOVERY
- Recover the database
- Run DBCC CHECKDB to ensure corruption is gone
- 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.
Paul
Nice Series! Why Backup the tail of the log after you’ve restored the page?
I would have thought it would make more sense to back the tail of the log before you restore the page?
Cheers
SQLSoldier
Hi Paul. In order to bring the file and its filegroup back online, it has to be brought current with the rest of the database. The tail log backup must include all transactions up to the point where you begin the recovery of the file. You can do it before as long as there are no other transactions occurring on the database, but it has to include everything up to the point of the page restore.