Backup and Data Storage is my most favorite subject and I have not written about this for some time. I was experimenting with new feature of SQL Server 2008 and I come across very interesting feature of Backup compression.
Let us see example of Database AdventureWorks with and without compression. After taking backup with compression enabled and without compression the file size can be compared to see the difference it makes with compressing the database.
BACKUP DATABASE AdventureWorks TO DISK='C:\Backup\AW_NoCompression.bak'
GO
BACKUP DATABASE AdventureWorks TO DISK='C:\Backup\AW_WithCompression.bak'
WITH COMPRESSION
GO
SQL Server 2008 supports backup data compression at database level. First of all enable advance option and set the server level setting for backup compression on. As backup compression settings are turned on all the future backup will be taken with backup compressions. There is no need to write any other extra clauses described in script above.
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
This is interesting feature should be use with care. Compression is based on what kind of the data is being compressed. If you are storing images in database it will be difficult to compress that kind of data. Taking compressed backup is better for input/output operations as there is less amount of the data has to be written on the database. Compressed backup is resource intense process, therefore it should be used with care. If database is busy all the time this feature should be turned off.
Yes, I plan to use this feature on my own production server when I upgrade my current servers to latest version.
Reference : Pinal Dave (https://blog.sqlauthority.com)
