By: Bru Medishetty | Updated: 2010-05-26 | Comments (4) | Related: > Compression
Problem
SQL Server 2008 introduced data compression and backup compression. Can you explain the impact of data compression when used with backup compression?
Solution
In SQL Server 2008 a database backup can be compressed to save the amount of disk space required for the backup. It can be performed on databases which use data compression and databases where data compression is not enabled.
In order to make things simple and for easier explanation, this example is carried out on a database which has a single table with millions of records in it. The properties window of the table is displayed in the image below. This tip is covered in 4 steps to ensure clarity with respect to size of the database backups without backup compression and data compression, backup compression without data compression and then look at backup compression for the two types of data compression.
Step 1 - Normal backup with no data compression
As the first step we shall take a backup of the database without backup compression. Remember the table does not have data compression enabled. The below code is used to perform the database backup.
TO DISK = N'O:\BackupDirectory\BackupWithoutDataCompression.BAK' WITH NOFORMAT,
NAME = N'LearnSQLWithBru-Full Database Backup Without Data Compression', NO_COMPRESSION
GO
The details of the database backup file is shown in the image below, note that the backup size is just about the same size as the database since the backup is not a compressed backup.
Step 2 - Compressed backup with no data compression
In the next step we shall look at the size of the backup file by compressing the backup. The below code is used to perform the database backup with compression.
TO DISK = N'O:\BackupDirectory\CompressedBackupWithoutDataCompression.BAK' WITH NOFORMAT,
NAME = N'LearnSQLWithBru-Full Database Compressed Backup Without Data Compression', COMPRESSION
GO
The details of the new database backup file is shown in the image below (highlighted with a blue background), note that the backup size is reduced to a third of the previous backup, which is common in the case of compressed backups in SQL Server 2008.
Step 3a - Normal backup with row-level compression
We now move to the next step in which we shall first compress the table using row level compression. After that we shall perform a normal database backup (without compression) and a compressed database backup. The table "dbo.DemoCompressionTable" needs to be compressed with row level data compression, which we are going to perform using the below command.
REBUILD WITH (DATA_COMPRESSION = ROW);
After the table is compressed, first we shall perform a normal backup of the database. The script below will perform a normal backup of the current database.
TO DISK = N'O:\BackupDirectory\NormalBackupWithRowCompression.BAK' WITH NOFORMAT,
NAME = N'LearnSQLWithBru-Full DB Normal Backup With Row Compression', NO_COMPRESSION
GO
The size of this backup file (performed in this step) is smaller than the normal backup file of the same database that we performed in Step 1. The main reason the backup file size is reduced is the decrease in the number of data pages required to store the table due to row level compression applied on the table.
Step 3b - Compressed backup with row-level compression
In this step we shall perform a compressed backup of the database and compare the results with those from the step without backup compression (Step 3a). The below script is used to perform a backup of the database with backup compression.
TO DISK = N'O:\BackupDirectory\CompressedBackupWithRowCompression.BAK' WITH NOFORMAT,
NAME = N'LearnSQLWithBru-Full DB Compressed Backup With Row Compression', COMPRESSION
GO
After successfully taking the backup, the below image displays the file size of the new backup along with the old ones. As you can see we got a little more compression.
What we can conclude with this step is backup compression (for a database with data compression) does not always compresses the backup file with the same ratio when compared to a database without data compression. If backup compression always compresses the backup with the same ratio, then in the current step we should have a backup file around 400 MB (approx), but we do not because the data is already compressed.
Step 4a - Normal backup with page-level compression
In this next step we shall first compress the table using page level compression. Next we will perform the same steps as we did in the previous steps, a normal database backup (without compression) and a compressed database backup. The following script will compress the table with page level data compression.
REBUILD WITH (DATA_COMPRESSION = PAGE);
After the table is compressed, first we shall perform a normal backup of the database. The script below will perform a normal backup of the current database.
TO DISK = N'O:\BackupDirectory\NormalBackupWithPageCompression.BAK' WITH NOFORMAT,
NAME = N'LearnSQLWithBru-Full DB Normal Backup With Page Compression', NO_COMPRESSION
GO
The size of this backup file (performed in this step) is drastically reduced when compared to normal backup file size of the same database that we performed in step 1. The main reason the backup file size is reduced drastically is because page level compression reduces the number of data pages required to store the table more than with row level compression. So from this we can see that page level compression get better compression than row level compression.
Step 4b - Compressed backup with page-level compression
We repeat the steps to perform a compressed backup of the database and compare the results with those from the step without backup compression (Step 4a). The below script is used to perform a backup of the database with backup compression.
TO DISK = N'O:\BackupDirectory\CompressedBackupWithPageCompression.BAK' WITH NOFORMAT,
NAME = N'LearnSQLWithBru-Full DB Compressed Backup With Page Compression', COMPRESSION
GO
Below is the list of backup files after the backup.
After this backup and comparing backup file sizes with those of the previous backups, backup compression does not significantly compress the backups when the database is already enabled with page level data compression.
Conclusion
Backup compression reduces the backup size by approximately 30% when compared to a regular non-compressed backup of the same database when page level compression is enabled. When row level compression is enabled, it reduces somewhat better, approximately 60 %.
Some points to be remembered
- The results shown in this article might differ with another database with same size due to differences in the datatypes of the columns and the data occupancy in the records.
- When page level compression is enabled on one table and row level compression is enabled on another table, the backup compression will be proportional in size with respect to the amount of data that exists in those tables.
- On a partitioned table, one partition can be compressed using row level compression and another using page level compression, i.e both types of compression can be enabled on a single table.
Next Steps
- Look for more information about compression on SQL Server official site
- Read this data compression tip on MSSQLTips Implementing Data Compression in SQL Server 2008
- Read this backup compression tip on MSSQLTips SQL Server 2008 Backup Compression
About the author
Bru Medishetty focuses on Performance Monitoring and Tuning as well as planning and implementing Disaster Recovery solutions.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-05-26
