Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Recovering data from corrupt tables using non-clustered indexes

By BJ Hermsen,

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
Total article views: 5618 | Views in the last 30 days: 2
 
Related Articles
FORUM

RowID in Sql Server

RowID in Sql Server

FORUM

corrupt index (nonclustered primary key) after 2005 upgrade

corrupt index (nonclustered primary key) after 2005 upgrade

FORUM

db page corruption

page corruption

FORUM

Partitioning key examples vs clustered index rules

Examples about partitioning do not follow the rules für clustered indexes. can someone combine those...

ARTICLE

Time for a ROWID?

Steve Jones looks to the future of SQL Server and wonders if we ought to add a rowid to the internal...

Tags
checkdb    
corruption    
dbcc    
repair_allow_data_loss    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones