Post reply

Fragmentation and backup/restore ??

  • September 20, 2011 at 5:24 pm

    #257759

    Say both data/log files and tables/indexes are totally fragmented on existing box, if we copy a full backup from existing box to a new box, and restore database , now questions:

    1) Would data/log files gona be defraged on new box ? Or i need to defrag them on existing box before i do full backup ?

    2) Would tables/indexes gona be defraged on new box ? Or I need to rebuild indexes on new box after restore ?

    Thanks.

    ddfg

  • September 20, 2011 at 6:31 pm

    #1384275

    Backup / restore is a perfect copy. Nothing changes (stats, indexes, data, fragmentation, etc).

    This will allow you to only work on the indexes that need to be defragmented / rebuilt.

    http://sqlfool.com/2011/06/index-defrag-script-v4-1

  • September 20, 2011 at 7:45 pm

    #1384288

    the script Remi referenced is pretty much the de-facto defrag script.

    And yes, you will need to defrag after a restore on a new box if the existing db was severely fragged.

    But you question also makes me wonder if you are referring to file system fragmentation. If you restore to a new system and there is adequate contiguous space, you will not have fragmented files. That is different than the contents of the data file will see be fragmented though. Make sense?

    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

  • September 21, 2011 at 5:54 am

    #1384424

    Option 1:[/u] You can either perform rebuild/defrag indexes on existing box before do the full backup

    or

    Option 2: rebuild/defrag indexes on the new box after the restore.

    Now, if your existing system is production server and do not allow you to rebuild/defrag indexes you can go with Option 2 else follow Option 1.

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • September 21, 2011 at 3:59 pm

    #1384871

    Thanks everyone. So, seems the answer is backup/restore will get rid of data/log files fragmentation ( i don't have to drag disks first on old box and do full backup), but will not fix the tables/indexes fragmentation ( they are still fragged on new box after restore ).

    ddfg

  • September 21, 2011 at 4:29 pm

    #1384874

    awu (9/21/2011)

    Thanks everyone. So, seems the answer is backup/restore will get rid of data/log files fragmentation ( i don't have to drag disks first on old box and do full backup), but will not fix the tables/indexes fragmentation ( they are still fragged on new box after restore ).

    Yes, but with the 1 caveat that you need to have enough contiguous space on the drive for the new db files.

Viewing 6 posts - 1 through 5 (of 5 total)

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