DYK: Restoring a Database Retains Index Fragmentation

I am not sure where the “myth” that restoring a database removes the index fragmentation, or that restoring a database causes a database to become fragmented came from recently, but twice in the last few days I have seen questions asked about the impact of restoring a database has on index fragmentation.  The first time was a quick Tweet from Jorge Segarra following SQL Saturday that prompted some discussion on twitter that I disagreed with and so set out to prove that a restored copy of a database has exactly the same fragmentation as the source database at the time of the backup.  To do this, I used AdventureWorks in a isolated VM on my laptop.

First I took a backup of the AdventureWorks database and then restored it to AdventureWorks3 on the same instance (I already had AdventureWorks2 on the instance from some other operation I was testing, but I have no idea what at this point in time).  Then I ran the following query which uses the EXCEPT operator to find Index stats from the source AdventureWorks database that aren’t in the restored AdventureWorks3 database and then unions the result set with the reverse EXCEPT to find index stats from the restored AdventureWorks3 database that aren’t in the source AdventureWorks database. 

SELECT * FROM
(
   SELECT  
       OBJECT_ID, index_id, partition_number, index_type_desc, 
       alloc_unit_type_desc, index_depth, index_level,
       avg_fragmentation_in_percent, fragment_count, 
       avg_fragment_size_in_pages, page_count, 
       avg_page_space_used_in_percent, record_count,
       ghost_record_count, version_ghost_record_count, 
       min_record_size_in_bytes, max_record_size_in_bytes, 
       avg_record_size_in_bytes, forwarded_record_count 
   FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),NULL,NULL,NULL,'DETAILED')
EXCEPT
   SELECT 
       OBJECT_ID, index_id, partition_number, index_type_desc, 
       alloc_unit_type_desc, index_depth, index_level,
       avg_fragmentation_in_percent, fragment_count, 
       avg_fragment_size_in_pages, page_count, 
       avg_page_space_used_in_percent, record_count,
       ghost_record_count, version_ghost_record_count, 
       min_record_size_in_bytes, max_record_size_in_bytes, 
       avg_record_size_in_bytes, forwarded_record_count 
   FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks3'),NULL,NULL,NULL,'DETAILED')
UNION ALL
   SELECT 
       OBJECT_ID, index_id, partition_number, index_type_desc, 
       alloc_unit_type_desc, index_depth, index_level,
       avg_fragmentation_in_percent, fragment_count, 
       avg_fragment_size_in_pages, page_count, 
       avg_page_space_used_in_percent, record_count,
       ghost_record_count, version_ghost_record_count, 
       min_record_size_in_bytes, max_record_size_in_bytes, 
       avg_record_size_in_bytes, forwarded_record_count 
   FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks3'),NULL,NULL,NULL,'DETAILED')
EXCEPT
   SELECT 
       OBJECT_ID, index_id, partition_number, index_type_desc, 
       alloc_unit_type_desc, index_depth, index_level,
       avg_fragmentation_in_percent, fragment_count, 
       avg_fragment_size_in_pages, page_count, 
       avg_page_space_used_in_percent, record_count,
       ghost_record_count, version_ghost_record_count, 
       min_record_size_in_bytes, max_record_size_in_bytes, 
       avg_record_size_in_bytes, forwarded_record_count 
   FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),NULL,NULL,NULL,'DETAILED')
) AS tab

The result returned is an empty result set, proving that index fragmentation is identical between the source and restored databases which is exactly what I expected to occur.  This has nothing to do with file system fragmentation of the data file, which is an entirely different subject, this is specific to logical fragmentation of indexes, and heaps inside the database file.