SQL Server: Script to enable Native Backup Compression for all Database Backups

In this post, I am sharing a basic about SQL Server backup compression and how to enable compression for all the database backups.

Why we require compressed backup?

Simple, Large amounts of data take a long time to write to disk and require almost same space of the database.

For example, when our database backup file is 50GB, database allocated size is also 50GB.
When the database data file has lots of free space, backup size is smaller than actual occupied database size.

The slowest thing in the backup process is usually writing the backup file, whether it’s over the network or to local disk.

SQL Server 2008 introduced a Native Compressed Backup option, in which you can take database backup using WITH COMPRESSION.

T-SQL Script to take a single backup using WITH COMPRESSION:

T-SQL Script to enable Database compression for all the Native backups:

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of