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 (http://www.SQLAuthority.com)
pinaldave
Hi,
Could you make more tests with compression backups, I would like to know how much time does it take to backup a larger database (lets say 20 GB) with and without compression.
Also, it would be interesting to know how much CPU and memory is used, do you know where is the data being stored while the server is compressing the file (memory or disk)?
Thanks.
Dear Sir,
I have seen ur blog. It is realy fantastic. One thing i want to know, i m working in a software company as a Customer support Executive by 2 years. Now i want to improve my carier in Sql DBA. So can please give me some suggestion for this.
Thanks
Mudit
[...] 6, 2008 by pinaldave In my previous article SQL SERVER - 2008 - Introduction to New Feature of Backup Compression I wrote about Row Compression and I have received many request to write in detail about Row [...]