This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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?
Because, Large amounts of data take a long time to take a backup and require almost same space of the database.
For example, when your database backup file size is 50GB, database actual 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:
|
1 2 3 |
BACKUP DATABASE AdventureWorks2012 TO DISK = D:\Backup\AW2012_compress.bak' WITH COMPRESSION |
T-SQL Script to enable Database compression for all the Native backups:
|
1 2 3 4 5 6 7 8 9 10 11 |
USE master; GO EXEC sp_configure 'show advanced option', '1'; RECONFIGURE GO EXEC sp_configure 'backup compression default', '1'; RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'show advanced option', '0'; RECONFIGURE GO |
