Systems Engineering and RDBMS

Back Up Compression Option in SQL Server 2008

Posted by decipherinfosys on January 3, 2008

We have been covering the new features of the upcoming release of the next version of SQL Server. You can search on “SQL Server 2008” on this blog and get to those posts. In this post, we will briefly cover the back up compression option that has been introduced in SQL Server 2008. All this code is valid as of CTP5.

First, let’s check the size of the database first.

Get the size of the databases

—————— ————-
AdventureWorks 174.63 MB

We will pick up the DECIPHER_TEST database for this post. We will run the back-up command first without specifying any compression and then next with compression.

Do the back-up – Uncompressed

TO DISK = ‘C:\Back_UP\DT.bak’

Do the back-up – Compressed
TO DISK = ‘C:\Back_UP\DT_COMP.bak’

For the un-compressed back-up, we got:
BACKUP DATABASE successfully processed 65985 pages in 118.665 seconds (4.555 MB/sec).

For the compressed back-up, we got:
BACKUP DATABASE successfully processed 65985 pages in 129.773 seconds (4.165 MB/sec).

The difference in size was: 528MB for the un-compressed vs 104MB for the compressed. All this was done on a very small VM but the difference in timings and the size of the back-ups should give you an idea on the difference between un-compressed and compressed back-ups. The compression ratio of course is dependent upon what kind of data do we have in the database. For example: The string data compresses better than other data. Now that we have backed up the database, how can we tell whether a particular back-up was done using compression option or not? For that, we can use the RESTORE HEADERONLY command and check the value under the COMPRESSED column. If that value is 1 then, it is a compressed back-up and if it is 0, then it is an un-compressed back-up. Example:

restore headeronly
from disk = ‘C:\Back_Up\DT_COMP.bak’
/*Abbreviated: Showing only the column we are interested in*/


restore headeronly
from disk = ‘C:\Back_Up\DT.bak’


And the compressed and un-compressed backups cannot be mixed up on the same file. Example: Trying to do a compressed back-up on a file that already has non-compressed back-up will give us 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.

Another thing to note is that you can also change the default behavior of the back-ups to be always compressed back-ups. You can do so by changing the instance level setting using the sp_configure command. Example:

EXEC sp_configure ‘backup compression default’, 1

You can choose to do so or do it only at the individual command level. We will be doing some testing with the RTM version (whenever it is released) on some real world databases to see the CPU overhead and the back-up time and will post the results on the blog. While restoring these back-up sets, there is nothing that needs to be specified to indicate that we are restoring a compressed back-up set. Now, while doing a restore – we saw that the restore from the compressed back-up took lesser time as compared to the restore from the un-compressed back-up. One would have thought that it would be the reverse since some cost will be associated with the un-compressing. This is something that we will research and post in a future blog post. Looks like the smaller size of the back-up set in the case of a compressed back-up off-sets some of that time.

3 Responses to “Back Up Compression Option in SQL Server 2008”

  1. […] by decipherinfosys on October 30, 2008 In one of our previous blog post, we had talked about backup compression feature in SQL Server 2008. In today’s post we are […]

  2. […] We had discussed backup compression feature in SQL Server 2008 in one of previous blog posts here.  What this parameter setting is used for is to specify whether we want compression to be enabled […]

  3. […] – Part 1 and Part 2.  We had also covered backup compression in one of the posts – here.  We have received quite a few questions from our readers about performance impacts as well as […]

Sorry, the comment form is closed at this time.

%d bloggers like this: