Post reply

Index Backup

  • October 28, 2019 at 4:46 pm

    #3692467

    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

  • October 28, 2019 at 5:18 pm

    #3692480

    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.

    Change is inevitable... Change for the better is not.

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

  • October 28, 2019 at 7:54 pm

    #3692521

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

  • October 28, 2019 at 7:58 pm

    #3692526

    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?

  • October 29, 2019 at 12:52 am

    #3692573

    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.

    Change is inevitable... Change for the better is not.

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

  • October 29, 2019 at 11:58 am

    #3692674

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • October 29, 2019 at 3:30 pm

    #3692788

    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/

  • October 29, 2019 at 6:47 pm

    #3692870

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

    --Vadim R.

  • October 29, 2019 at 7:05 pm

    #3692876

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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