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)
20 Comments. Leave new
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
Hi Dave,
This is very interesting feature. But currently i instaled SQL 2008 Express with Tool, in the server option i see the Compress Backup checkbox, then i checked in. But it does not affect.
Then i tried your SQL statement to enable this by command. But it does not works too.
Did this feature avaiable in the express version?
Whats the Ratio of Backup file with Compression and without Compression
Does it affect the restore of the DB ?
Thanks
Hello Zafar,
The ratio of backup depends of the data of database. Few factors like data type of data, consistency of data, whether data is already compressed or encripted decides the compression ratio. But normally comprassion is 40% to 80% of uncompressed backup size.
You can check the ratio in the “Backup_size” and “Compressed_backup_size” columns of backupset table.
Backup as well as resotre are faster on comprassed backup. The only thing that increases is CPU utilization.
Regards,
Pinal Dave
Dear Sir,
Is the compress backup functionaly only support Microsoft SQL Server 2008 Enterprise edition?
I tried to executed on my productin SQL Server 2008 Standard edition:
EXEC sp_configure ‘backup compression default’, ‘1’;
RECONFIGURE WITH OVERRIDE;
GO
It retured error of:
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51. The configuration option ‘backup compresion defulat’ does not exist, or it may be an advanced option.
Thanks,
Edwin
This is a different subject.
I have some very large table which I will like select all the data in those tables to different tables by using select into. Also, I want to set the row count to 100000 at a time until all the rows a select into the new table with a loop. Here is the code I have and it is not working properly. Can you help?
SET ROWCOUNT 100000
DECLARE @RCOUNT INT
SET @RCOUNT = 10000
WHILE @RCOUNT = 10000
BEGIN
BEGIN TRAN
INSERT Tbl_archive2
SELECT * FROM archive
SELECT @RCOUNT = @@rowcount
COMMIT TRAN
CHECKPOINT
END
go
Is It possible in SQL Server 2005?
I want backup compression feature in SQL Server 2005. How can I get it? Please help me.
Hi… Pinal,
Before implementing backup compression, i thought of going through Microsoft website… and i found this info..
The following restrictions apply to compressed backups:
Compressed and uncompressed backups cannot co-exist in a media set.
NTbackups cannot share a tape with compressed SQL Server backups.
Can you please explain, what does it mean….
Hi,
I recently used the backup in my project so did some R&D and below are the results for Windows compression v/s DB compression:
I used a DB with no image data and take 2 backups one with compression and another without.
DB backup w/ compression – 6.49 MB
DB backup w/o compression – 48.2 MB
Now when I applied windows compression i.e. winzip the results were:
DB backup w/ compression – 5.53 MB
DB backup w/o compression – 4.99 MB
I made my choice to go with normal backup and then compress it with winzip :)
Thanks
Anki
Ankit,
Winzip won’t work with heavy files like files of 40 – 50 GB or more.
Also, it’s a dual overhead. Means, you take a regular backup which takes time and then you zip it using winzip which again takes time. If you want to restore the database, you unzip the file (time taking process) and then restore (time taking process again).
If you take a compressed backup it’ll restore faster than the normal backup.
My experience:
Backup file size w/o Compression: 250 GB. Backup time around 2 hours.
Restoration time around 2 hours.
Backup file size with Compression: around 60 GB. Backup time around 30 minutes. Restoration time around 30 minutes.
Regards
Mayur Kashikar
Hi..
Is there any feature that allows us to set the compression level as well? (Compression level as “Lowest, “Normal” or “Highest”). I ask this because i was just going through a utility (for SQL db backup) that allows me to select either of the mentioned compression level settings.
Please note that the utility provides me a “.zip” file as backup. So I am not sure whether this utility is using the compression feature of SQL or the WinZip.
Request you to please throw some light.
Please post a script for the restore using compression. Thanks
This option is only available on 2008 Enterprise. 2008 R2 includes standard.
Correct David. Official links for others to refer
SQL Server 2014
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-2017
Enterprise, Business Intelligence,Standard
SQL Server 2012
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/cc645993(v=sql.110)
Enterprise, Business Intelligence,Standard
SQL Server 2008 R2
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/cc645993(v=sql.105)
Backup compression for Standard edition of SQL Server is supported only in SQL Server 2008 R2 or higher versions.
SQL Server 2008
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/cc645993(v=sql.100)
Enterprise
Hello Pinal
It doesnt work on SQL 2008
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server?view=sql-server-2017
If Ms doesn’t support compression in sql 2008 then why it is visible?
Sir i am an newbie i used with compression and getting this error
Msg 3098, Level 16, State 2, Line 1
The backup cannot be performed because ‘COMPRESSION’ was requested after the media was formatted with an incompatible structure. To append to this media set, either omit ‘COMPRESSION’ or specify ‘NO_COMPRESSION’. Alternatively, you can create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
SIr You really Save my time because many this i have learnt from your blogs
Thanks
Pinal I just want to thank you for your efforts in creating a steady stream of useful articles on SQL. You cover each topic well and provide helpful examples. When I’m stuck on something and hit Google I always click your link first. Keep up the great work! – Robert
Thanks @Robert. I am glad that you liked my efforts. It made my day.