Defrag or Backup+Restore

Question

I have an MDF file that was autogrown from 1MB to 40GB in 10% increment. The file is heavily fragmented now, and I want to defrag it with minimum downtime.

I have a plan that I am not sure will work:

1. Normally defrag the partition while the service is still running until the only fragmented files are the database files.
2. Enter single user mode.
3. Make complete backup to a different partition.
4. Delete the database with fragmented MDF file.
5. Create/restore new database from the recently created backup.

Will dropping database and then restore from backup remove the file fragmentation?

Or should I just turn off the service and run defrag tool until it finishes?

Answer 1

There is a great discussion of this topic here at ServerFault in which the 3 forms of fragmentation are discussed. I always have to remind myself to search for the answer before I post a question as it saves me so much time.

Short answer (tl/dr):

It will remove physical fragmentation but not logical or VLF fragmentation within the log file.

Physical Fragmentation:

Backing up and restoring will remove any physical fragmentation. That is, fragmentation that occurred on the NTFS partition as a result of your database growing incrementally over time and contiguous space not being available as a result.

Other Fragmentation:

You will still have index fragmentation and VLF Fragmentation within the database. Paul Randall stressed not using Windows Defrag at his SQLSkills Immersion Events, and how it could lead to database corruption so I think you're on the right track.

Background:

Physical fragmentation will operate on the disk where the MDF/NDF/LDF files occupy. Since SQL Server does not track this information as it would be useless. We want to be able to restore databases to any hard drive as long as it has space, we're not concerned with where it gets located on the disk so much unless you're short writing...but that's a exception to the rule.