SQL Backup removes fragmentation ?

  • Our database is already 90% fragmented. Does SQL Server backup removes the fragmentation ? If it does, then all I need to do is just backup and restore. Any comments ?

  • jimmycjen (6/22/2009)


    Our database is already 90% fragmented.

    Index fragmentation or file-system fragmentation?

    Does SQL Server backup removes the fragmentation ?

    All SQL backup does is read through the database file and write out a backup file. It makes no significant changes to the database (just sets some last backup info in the header)

    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
  • I mean file fragmentation.

    I noticed the backup file size is always smaller than the actual database file. That's why I thought if SQL Backup will defrag its files.

    What if I backup the database, then drop it and restore it, will database file remain fragmented ?

    Thanks for replying.

  • jimmycjen (6/22/2009)


    I noticed the backup file size is always smaller than the actual database file.

    Because the database file may have some empty space and the backups just contain the data, not the empty space

    What if I backup the database, then drop it and restore it, will database file remain fragmented ?

    Maybe. It depends what the file system looks like. If, on disk, there's no contiguous free space for the file to go, then it will be fragmented because there's no way it can be put on disk in a single chunk.

    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 4 posts - 1 through 3 (of 3 total)

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