Index Backup

  • Nita Reddy

    Hall of Fame

    Points: 3976

    Rather than rebuilding Index, Can I take at Backup of Index and restore?

    I have noncluster Index which takes 5 hours to rebuild. Is there any way we can do backup and restore so I don't have to rebuild.

    i have application which drops indexes

  • Jeff Moden

    SSC Guru

    Points: 995109

    To answer the first question... NO.

    To answer the implied question of how to make this faster, we need some more data.  We need to know ...

    1. The definition of the index.
    2. The definitions of the columns involved (including column widths for any variable width columns and whether or not any of them have the IDENTITY property) .
    3. Whether or not the index in question is being used as a constraint or is in anyway involved in a constraint such as an FK.
    4. How many rows are in the table.
    5. All that same info for the Clustered Index.
    6. What the exact code you use to do the index REBUILD.  Be sure to include whether or not it's being done ONLINE or not, with PADDING or not, what the Fill Factor is, etc.
    7. What the normal level of logical fragmentation and page density normally is when you do a REBUILD.
    8. If you know the "Insert/Update/Delete" pattern, that would be helpful, as well.  For example, you do an INSERT... how soon after that is the row updated or deleted and, if updated, which columns in the index are generally updated?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Nita Reddy

    Hall of Fame

    Points: 3976

    Thanks just want to make sure what if index is created in a file group can we restore that only?

  • crow1969

    SSCrazy

    Points: 2909

    In any RDBMS (SQL Server, Oracle, MySQL, etc.) when you do a restore, you have to have all of the files, and all of the pages agree what version you restore back to.  Consider this:

    1. You "back up" the index
    2. Users add entries to the table
    3. You "restore" the index

    Will the restored index have entries for the new values?  If so, where would they come from?

  • Jeff Moden

    SSC Guru

    Points: 995109

    Nita Reddy wrote:

    Thanks just want to make sure what if index is created in a file group can we restore that only?

    Even if we could, restoring an index will not defrag it.  If the index were rebuilt outside the database, then the effect that Crow1969 described would take over.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Grant Fritchey

    SSC Guru

    Points: 395634

    It is an interesting question. I suppose if there were zero data changes, you probably could just restore the index in a piecemeal restore if it's on a different file group. However, that immediately begs the question, if there are no data changes, why are you rebuilding it? So, there probably are data changes. Then, the question is, why are you rebuilding it? Are you attempting to defrag it? In which case, you really owe it to yourself to look up Jeff's articles on that. Are you trying to maintain statistics? You don't need to rebuild to do that.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Michael L John

    One Orange Chip

    Points: 25796

    Are you rebuilding this index because it is fragmented?  If it is fragmented, how much?

    As Grant suggested, it probably makes sense to read some of the articles published by Jeff Moden on this subject.

    https://www.sqlservercentral.com/articles/rebuild-index

    To relate my situation, we have not done re-indexing on anything for quite a while.  This decision was based upon a lot of testing, and a few leaps of faith based upon some of the information published by Jeff (and few phone calls!).

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • rVadim

    Hall of Fame

    Points: 3922

    Maybe OP re-creating index, not rebuilding it? Last sentence ("i have application which drops indexes") kind of implies that?

    --Vadim R.

  • Grant Fritchey

    SSC Guru

    Points: 395634

    rVadim wrote:

    Maybe OP re-creating index, not rebuilding it? Last sentence ("i have application which drops indexes") kind of implies that?

    Could be, but recreating an index after data changes requires pretty much rebuilding the index. There just isn't a way to store indexes such that they can be "restored" just due to the nature of changing data.

    We could talk about the possibility of sharding or partitioning in support of management, but not based on the info we have so far.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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