Post reply

The Effect of Reindexing on a backup file

  • December 12, 2013 at 4:48 am

    #285941

    Hello,

    I am trying to find out the impact of a database reindex on an SQL Server backup.

    The scenario is as follows:

    I have a database called ABC that I perform a backup of to a flat file using the command in a SQL Agent job - BACKUP DATABASE ABC TO DISK = 'C:\Backups\ABC.DAT' WITH INIT

    Now I perform some defragging of indexes in the database using REBUILDS for those with a fragmentation over 30% and a REBUILD for over 10%.

    The database remains in FULL mode while this is running.

    Once complete I perform another backup of the database to a flat file using the same method as before - BACKUP DATABASE ABC TO DISK = 'C:\Backups\ABC.DAT' WITH INIT in a SQL Agent job.

    What I would like to know is if the data held within the backup file has changed its order?

    Regards,

    Steve

  • December 12, 2013 at 4:49 am

    #1672893

    Very likely.

    Why?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 12, 2013 at 4:56 am

    #1672895

    Hi Gail,

    Thank you for the speedy reply.

    The reason for the question is because I am having a debate with my EMC backup admins about the unique data the backups present to our Data Domain nightly. I am of the view that once we defrag the indexes and perform the backup again to a file the blocks in the file have moved around, so they will be seen as unique by the data domain which uses deduplication.

    Hope I am making sense.

  • December 12, 2013 at 5:19 am

    #1672899

    Depends how big the blocks that the dedupe looks at, but yes, probably they will have changed. This is not a good reason to not rebuild indexes

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 12, 2013 at 5:31 am

    #1672902

    Hi Gail,

    I have not intention to stop the rebuilding of the indexes.

    Just trying to help the EMC team work out what is causing the growth as the data domain seems to be a black box even to them.

    It is running low on space.

    Regards,

    Steve

  • December 12, 2013 at 5:32 am

    #1672903

    Hi Gail,

    Do you have expereince of working with Data Domains and deduplication?

    Regards,

    Steve

  • December 12, 2013 at 6:59 am

    #1672937

    Only in the 'black box' sense.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 12, 2013 at 7:05 am

    #1672940

    OK

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

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