Recovering data from corrupt tables using non-clustered indexes

Question

Recovering data from corrupt tables using non-clustered indexes

BJ Hermsen

Hall of Fame

Points: 3123
More actions
January 18, 2010 at 11:20 pm

#215651

Comments posted to this topic are about the item Recovering data from corrupt tables using non-clustered indexes

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

jasonwhowell SSC Veteran Points: 286 More actions · Answer 1

Its important to note that the Non-clustered index DOES NOT contain the data of all columns. The data of all columns (except BLOBs/Text) in a row in SQL Server usually lives in the leaf level pages of the Clustered Index (if there is one). The NC index contains the key value in the covered columns of the NC index, and a uniqueified clustered index key value to locate the clustered index leaf pages, or the File ID+Row ID in the storage heap if there is no clustered index on the table. SQL 2005 and SQL 2008 offer "Included Columns" where the non-key data can be included in the NC index to help reduce navigational costs to get to the clustered index pages, however that is optional.

Therefore, while navigation to get to row values using the NC Index is a good technique when you are out of options, it does not guarantee that you will get to the data when the database has corruption. If the clustered index leaf pages are corrupt, the data is corrupt. If only the intermediate or root page in the clustered index is corrupt, maybe using the NC indexes to get to the rows would work.

See also

-- NonClustered Index Structures http://msdn.microsoft.com/en-us/library/ms177484.aspx

-- Clustered Index Structures http://msdn.microsoft.com/en-us/library/ms177443.aspx

-- Included Columns http://msdn.microsoft.com/en-us/library/ms190806.aspx

BJ Hermsen Hall of Fame Points: 3123 More actions · Answer 2

Completely agree with you Jason. This is that 'Last Ditch Effort' that nobody ever wants to have to resort to. Becomes the some data is better than no data scenario.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

The best resolution for database corruption is to restore to the last known good backup.

I am with you here.

This is unfortunately not always possible.

But then you immediately lost me here. To quote Paul Randal "You don't have a backup until you've done a restore." If you can't restore your database from a backup, you haven't done your job as a DBA.

The idea behind your article is great (I'd give you a 4 out of 5), but I think you missed the opportunity to drill it in that backup/restore should be a priority and that the process in your article is nothing more than a last ditch effort to salvage anything possible. If the table has LOB or BLOB columns, you won't be able to recover those because they can't be indexed with a NCI.

icata Hall of Fame Points: 3176 More actions · Answer 4

icata

Hall of Fame

Points: 3176

January 19, 2010 at 8:07 am

#1105443

Great idea

Charles Kincaid SSChampion Points: 13593 More actions · Answer 5

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.

Well you learn something new every day. I was not aware of this. Thanks.

:ermm: Please tell us that you are not advocating adding covering indexes to hide database corruption and make folks feel better about less than adequate backups. Surely not.

ATBCharles Kincaid

BJ Hermsen Hall of Fame Points: 3123 More actions · Answer 6

Absolutely not. I am the first to stand up and say that having and testing backups is of the top priority. As stated earlier, this is the last ditch effort. I do not advocate creating extra indexing just in case however knowing ways to pull yourself from the fires of hell can sometimes be helpful.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

Charles Kincaid (1/19/2010)
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.
Well you learn something new every day. I was not aware of this. Thanks.

That isn't really a accurate description. The BOL description in Clustered Index Structures is:

In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list.

The Clustered Index holds the data for the table, there are no pointers back to a rown in the table, it is just a doubly linked list. That might be what you were going for, but the way you word that makes the concept change slightly.

sjimmo SSChampion Points: 11139 More actions · Answer 8

Having been there a couple of times over the years, I was glad to see this in writing. Igt is a fact of life, that sometimes you cannot use the backups. For a variety of reasons some places perform their backups knowing that the corruption is there but make the deliberate decesion to not fix it until later. I have done conversions where I have had to peek around the corruption using these methods. Thanks.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

I like this idea, purely as the very last ditch effort.

Good job.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events