Should I use Backup Compression or NTFS Compression for Backups?

Published On: 2015-05-06By:

There’s a few different ways to compress your database backups when backing them up to disk. The two easiest ones are database backup compression (using the COMPRESSION parameter in the BACKUP DATABASE command) or turning on NTFS compression on the folder. Recently I was working with a client doing a database migration and their old scheme used NTFS compression while I was doing SQL backup compression on the server that they were migrated to.

So this sounded like a perfect time to do a little testing while other backups and restores were running.

The backup files that I’m playing with are from a SQL 2014 database. The database was backed up to a database backup file using SQL compression, then to a database backup file without using compression. Then the uncompressed backup file was copied to the old Windows 2008 R2 server to see how well NTFS handled the compression.

The database it self is about 4768.63 MB in size with 1180.09 MB free space within the database.

The SQL compressed database backup was about 38 Megs give or take. The SQL uncompressed database backup was about 3.5 Gigs which is basically what you’d expect as that’s how much data is in the database.


So the SQL Backup Compression here is pretty damn good. So taking those same files and copying them to a folder which has NTFS compression we see that uncompressed backup has been compressed to 1.28 Gigs while the compressed backup has been compressed to 38 Megs (NTFS compression saves all of 512 bytes according to the screenshot below).



So if you are looking for a way to store your backups in a compressed format, SQL Backup Compression is going to be the way to go. The good news is that SQL Backup Compression was first introduced in SQL Server 2008 but as an Enterprise Edition feature. It was moved into the standard edition in SQL Server 2008 R2 so almost every production SQL Server out there should be able to use it (or at least most of them).


(As always when looking at compression metrics from someone, your mileage may vary depending on the kind of data in the database, the alignment of the moon, the color of the sky, etc.)

Contact the Author | Contact DCAC


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link