Occasionally a database will become corrupt. This is typically the result of a hardware failure which may be extremely small and unnoticed or a large system failure. The best resolution for database corruption is to restore to the last known good backup. This is unfortunately not always possible. This article will walk through the process of extracting data from Non-Clustered indexes when corruption blocks access to the same set of data from the base table.
In the event that a backup of the database prior to the corruption is not available, repairs will have to be made to the database to allow it to function again. Running these repairs may cause data loss, and the degree to which this happens may be extremely difficult to identify, if even possible. Restoring a secondary copy of the database (without repairing) will allow access to data that may become inaccessible from the repair process.
Even if the data is not available from the base tables due to the corruption it may be possible to retrieve data from the indexes. This is feasible when the affected column(s) have non clustered indexes created on them. One of the major differences between Clustered and Non-Clustered indexes is how they store the data. Clustered Indexes remove the data from the base table and store the data along with the pointer to the row in the table in the Clustered Index. A Non-Clustered index stores the data separately from the table which allows for multiple sort orders as well as other performance building access paths to be leveraged. As the data is stored in addition to the data in the table it does require additional writes on inserts and a strong potential for additional memory contention for reads however if implemented properly can give both added performance as well as added flexibility.
Below are the object creation scripts used for the examples in this article.
CREATE NONCLUSTERED INDEX NC_IndexA ON Example
(
RowID asc
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX NC_IndexB ON Example
(
KeywordB asc,
RowID asc
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX NC_IndexC ON Example
(
KeywordC asc,
RowID asc
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX NC_IndexD ON Example
(
KeywordA asc,
KeywordC asc,
RowID asc
) ON [PRIMARY]
Scenario
There was corruption on the base table affecting the data between RowID 10000 and 20000. As the indexes are non clustered indexes we have a duplicate copy of the data. A query to return all columns from the table that attempts to read into the affected range will fail with an error pointing to the corruption. Likewise an insert into that range will also fail.
Select RowID, KeywordB, KeywordA, KeywordC from Example where RowID = 10234
/* Fails due to encountering database corruption as there is no covering index */
A query to return information covered completely in an index should complete successfully if the query optimizer chooses to use the covering index. If the optimizer still chooses a table scan based on the optimizer statistics the query will fail.
Select RowID, KeywordB from Example where RowID = 10234
/* Succeeds if the Query Optimizer Chooses to use NC_IndexB */
Using the 'With (Index=_)' query hint you can force the optimizer to look in the index and not to look at the table. This can aid in rebuilding the base table and regaining some data that was displaced.
Example
The query fails due to database corruption being encountered when the row lookup attempts to access the keywords associated with the RowID. At this point the query will need to be broken apart a bit to recover the data. It is imperative to know the index design and the index ids. Assuming that the index ids are 1,2,3, and 4 respectively, queries can be written to pull data from the index data pages in lieu of the table. Two to three queries are needed to return the same data as the query above that failed. Below is the two query method utilizing one single keyword index (NC_IndexB) as the composite index (NC_IndexD).
select RowID, KeywordB from Example with (index = 2) where RowID = 10234
/* Returns RowID and KeywordB values where RowID = 10234 from NC_IndexB */
select RowID, KeywordA, KeywordC from Example with (index = 4) where RowID = 10234
/* Returns RowID, KeywordA and KeywordC where RowID = 10234 from NC_IndexD */
As the queries are completely satisfied by the data in the index they do not need to do a row lookup from the base table. This data can be inserted into a temporary table or into the base table of the repaired database thus allowing access through normal methods in the future.
Although this process would never be needed in the perfect world we all know that issues happen, people fail, jobs don't run and systems don't get monitored regularly. This process will aid in the recovery of data when all else fails and that last ditch effort is necessary. It is always nice to have tools available when all else fails so that some if not all data can be recovered. This process will not be easy nor quick on large systems however it could mean the difference between catastrophic data loss and a few days of lost productivity.
Side Notes:
To extract the index id information for a given table use the following script
Select
sysobjects.id 'TableObjectID'
, sysobjects.Name 'TableName'
, sysindexes.indid 'IndexID'
, sysindexes.name 'IndexName'
, syscolumns.name 'ColumnName'
, syscolumns.colorder 'ColumnOrder'
from sysindexes
inner join sysobjects
on sysindexes.id = sysobjects.id
inner join syscolumns
on syscolumns.id = sysindexes.id
where sysobjects.name = 'Example' --Table where data is needed
and sysindexes.indid <> 0 --Eliminate base table
