SQL Server with Mr. Denny

May 6 2015   6:00PM GMT

Should I use Backup Compression or NTFS Compression for Backups?

Denny Cherry Denny Cherry Profile: Denny Cherry

Tags:
SQL

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.

sql_backups

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).

test

test_compressed

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).

Denny

(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.)

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: