February 23, 2015

Difference b/w Normal / NoCompressed Backup Files in SQL

An SQL Query that gives me 3-4 times better performance and less size than Normal / NoCompressed Backup

Compressed Backup


BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabaseCompression.BAK' WITH COMPRESSION ,INIT

--BACKUP DATABASE successfully processed 172926 pages in 40.806 seconds (34.715 MB/sec).

Not Compressed Backup

BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabaseNoCompression.BAK' 


--BACKUP DATABASE successfully processed 172926 pages in 111.007 seconds (12.761 MB/sec). 

See the time taken; its almost 1/3 of NoCompression Backup. The size of the backup files Compressed is 197 MB and Uncompressed is 1386 MB.


No comments:

Post a Comment