Recovering data from corrupt tables using non-clustered indexes

  • BJ Hermsen

    Hall of Fame

    Points: 3123

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

  • jasonwhowell

    SSC Veteran

    Points: 286

    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

    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

    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.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • icata

    Hall of Fame

    Points: 3176

    Great idea

  • Charles Kincaid

    SSChampion

    Points: 13593

    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

    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

    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.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • sjimmo

    SSChampion

    Points: 11139

    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: 281252

    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

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

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