Post reply

Will Backup with compression help defragment the database?

  • January 29, 2014 at 8:37 am

    #303517

    We typically run snapshots of our production databases and restore them to a QA environment. The backup runs with compression option. Will backup with compression help defragment the databases? Because we have seen the same job runs much faster on QA environment than on production. So I'm suspecting this may be the cause(?)

    Here is our backup script....

    -- Backup Script

    BACKUP DATABASE [DBName]

    TO DISK = N'K:\SQLBackups\DBName_Snapshot.bak'

    WITH

    COPY_ONLY,

    FORMAT,

    COMPRESSION,

    INIT,

    NAME = N'Full Backup of DBName COPY ONLY snapshot',

    SKIP,

    STATS = 10;

    GO

  • January 29, 2014 at 8:51 am

    #1684348

    No. Back up does not defrag the DB.

    Your QA is better because it is less busy than prod.

  • January 29, 2014 at 10:27 am

    #1684412

    Backups have nothing to do with index fragmentation. Backups don't care about fragmentation, they don't try to decipher any of the pages. All a backup does is copy data from the source DB to the backup file

    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

Viewing 3 posts - 1 through 2 (of 2 total)

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