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:
BACKUP DATABASE AdventureWorks2012
TO DISK = D:\Backup\AW2012_compress.bak'
T-SQL Script to enable Database compression for all the Native backups:
EXEC sp_configure 'show advanced option', '1';
EXEC sp_configure 'backup compression default', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'show advanced option', '0';
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.