rsocol

Proof that index fragmentation affects backup size

Jun 20th, 2021
985
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE DATABASE TestBackupSize ON (NAME='TestBackupSize_Data', FILENAME='C:\SQLDATA\MSSQL15.SQL2019\MSSQL\DATA\TestBackupSize_Data.mdf', SIZE=1000MB)
  2. GO
  3. USE TestBackupSize
  4. GO
  5.  
  6. CREATE TABLE TestTable (
  7.     ID INT PRIMARY KEY,
  8.     Filler CHAR(100) NOT NULL DEFAULT ''
  9. )
  10.  
  11. INSERT INTO dbo.TestTable (ID)
  12. SELECT ROW_NUMBER() OVER (ORDER BY v1.low) FROM master.dbo.spt_values v1 CROSS JOIN master.dbo.spt_values v2
  13.  
  14. BACKUP DATABASE TestBackupSize TO DISK='TestBackupSize_1_NoCompression.bak' WITH NO_COMPRESSION -- 724MB
  15. BACKUP DATABASE TestBackupSize TO DISK='TestBackupSize_1_Compressed.bak' WITH COMPRESSION -- 28MB
  16.  
  17. DELETE dbo.TestTable WHERE ID%10<>0
  18.  
  19. BACKUP DATABASE TestBackupSize TO DISK='TestBackupSize_2_NoCompression.bak' WITH NO_COMPRESSION -- 724MB
  20. BACKUP DATABASE TestBackupSize TO DISK='TestBackupSize_2_Compressed.bak' WITH COMPRESSION -- 28MB
  21.  
  22. ALTER TABLE dbo.TestTable REBUILD
  23.  
  24. WAITFOR DELAY '0:0:10'
  25.  
  26. BACKUP DATABASE TestBackupSize TO DISK='TestBackupSize_3_NoCompression.bak' WITH NO_COMPRESSION -- 77MB
  27. BACKUP DATABASE TestBackupSize TO DISK='TestBackupSize_3_Compressed.bak' WITH COMPRESSION -- 3MB
  28.  
  29. USE master
  30. GO
  31. DROP DATABASE TestBackupSize
RAW Paste Data