Do you benefit from compressed backups if your data is compressed already? This was a question that was asked recently; I thought that the question was interesting and decided to do some testing.

Here is what we will do; we will create two databases with only one table. In one database we will use page level compression and in the other database we won’t use compression. After that we will backup each database twice, once with backup compression and once without backup compression. After that, we will do restore from these backups. After doing all these, we will find out if data compression makes backup compression faster. We will also find out if using backup compression on a database that uses data compression will result in a smaller backup file. At the bottom of this post you will see a table that has all these numbers in one spot.

Setup scripts

Let’s get started, first we will create the database that will not use compression

T-SQL
1
2
3
4
5
6
7
8
9
10
11
USE [master]
GO
 
CREATE DATABASE [TestUncompressed] ON  PRIMARY 
( NAME = N'TestUncompressed_Data', FILENAME = N'C:DbTestTestUncompressed_Data.mdf'   )
 LOG ON 
( NAME = N'TestUncompressed_Log', FILENAME = N'C:DbTestTestUncompressed_Log.ldf'  )
GO
 
ALTER DATABASE [TestUncompressed] SET RECOVERY  SIMPLE
GO
USE [master]
GO

CREATE DATABASE [TestUncompressed] ON  PRIMARY 
( NAME = N'TestUncompressed_Data', FILENAME = N'C:DbTestTestUncompressed_Data.mdf'   )
 LOG ON 
( NAME = N'TestUncompressed_Log', FILENAME = N'C:DbTestTestUncompressed_Log.ldf'  )
GO

ALTER DATABASE [TestUncompressed] SET RECOVERY  SIMPLE
GO

Now we will create the database that will use data compression

T-SQL
1
2
3
4
5
6
7
8
9
10
USE [master]
GO
CREATE DATABASE [TestCompressed] ON  PRIMARY 
( NAME = N'TestCompressed_Data', FILENAME = N'C:DbTestTestCompressed_Data.mdf'   )
 LOG ON 
( NAME = N'TestCompressed_Log', FILENAME = N'C:DbTestTestCompressed_Log.ldf'  )
GO
 
ALTER DATABASE [TestCompressed] SET RECOVERY  SIMPLE
GO
USE [master]
GO
CREATE DATABASE [TestCompressed] ON  PRIMARY 
( NAME = N'TestCompressed_Data', FILENAME = N'C:DbTestTestCompressed_Data.mdf'   )
 LOG ON 
( NAME = N'TestCompressed_Log', FILENAME = N'C:DbTestTestCompressed_Log.ldf'  )
GO

ALTER DATABASE [TestCompressed] SET RECOVERY  SIMPLE
GO

The following block of code will create a table with 3 million rows in the database that will not use compression

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
USE [TestUncompressed]
GO
 
 
CREATE TABLE Test (SomeCol INT NOT NULL,
                    SomeDate DATETIME NOT NULL,
                    SomeChar CHAR(30) NOT NULL,
                    SomeGuid UNIQUEIDENTIFIER NOT NULL)
                    
                    
GO
 
CREATE CLUSTERED INDEX ci_test ON Test(SomeCol, SomeDate, SomeChar)
GO
 
INSERT Test (SomeCol, SomeDate, SomeChar, SomeGuid)
SELECT x.Number, 
    CONVERT(DATE,DATEADD(mi,Number,'20100101')),
    DATENAME(MONTH,(CONVERT(DATE,DATEADD(hh,Number,'20100101')))) + '2010',
    NEWID()
    
     FROM (                 
SELECT TOP 3000000 ROW_NUMBER() OVER (ORDER BY s.id) AS Number
 FROM master..sysobjects s 
CROSS JOIN master..sysobjects s2) x
USE [TestUncompressed]
GO


CREATE TABLE Test (SomeCol INT NOT NULL,
					SomeDate DATETIME NOT NULL,
					SomeChar CHAR(30) NOT NULL,
					SomeGuid UNIQUEIDENTIFIER NOT NULL)
					
					
GO

CREATE CLUSTERED INDEX ci_test ON Test(SomeCol, SomeDate, SomeChar)
GO

INSERT Test (SomeCol, SomeDate, SomeChar, SomeGuid)
SELECT x.Number, 
	CONVERT(DATE,DATEADD(mi,Number,'20100101')),
	DATENAME(MONTH,(CONVERT(DATE,DATEADD(hh,Number,'20100101')))) + '2010',
	NEWID()
	
	 FROM (					
SELECT TOP 3000000 ROW_NUMBER() OVER (ORDER BY s.id) AS Number
 FROM master..sysobjects s 
CROSS JOIN master..sysobjects s2) x

The following block of code will create a table with 3 million rows in the database that will use data compression

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
USE [TestCompressed]
GO
 
 
CREATE TABLE Test (SomeCol INT NOT NULL,
                    SomeDate DATETIME NOT NULL,
                    SomeChar CHAR(30) NOT NULL,
                    SomeGuid UNIQUEIDENTIFIER NOT NULL)
GO
                    
CREATE CLUSTERED INDEX ci_test ON Test(SomeCol, SomeDate, SomeChar)
WITH (DATA_COMPRESSION = PAGE)
GO
                    
                    
 
INSERT Test (SomeCol, SomeDate, SomeChar, SomeGuid)
SELECT * FROM [TestUncompressed]..Test
USE [TestCompressed]
GO


CREATE TABLE Test (SomeCol INT NOT NULL,
					SomeDate DATETIME NOT NULL,
					SomeChar CHAR(30) NOT NULL,
					SomeGuid UNIQUEIDENTIFIER NOT NULL)
GO
					
CREATE CLUSTERED INDEX ci_test ON Test(SomeCol, SomeDate, SomeChar)
WITH (DATA_COMPRESSION = PAGE)
GO
					
					

INSERT Test (SomeCol, SomeDate, SomeChar, SomeGuid)
SELECT * FROM [TestUncompressed]..Test

Backups

Now it is time to do the backups, we will do 4 backups. Each database will be backed up compressed and uncompressed. Here is the code that will do that. I use the C:DbTest folder, if you want to run this, code make sure that you create that folder.

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
BACKUP DATABASE TestCompressed TO DISK='C:DbTestTestCompressedBackupCompressed.bak' 
WITH  COMPRESSION
GO
 
 
BACKUP DATABASE TestCompressed TO DISK='C:DbTestTestCompressedBackupUnCompressed.bak' 
GO
 
 
BACKUP DATABASE TestUncompressed TO DISK='C:DbTestTestUncompressedBackupCompressed.bak' 
WITH  COMPRESSION
GO
 
 
BACKUP DATABASE TestUncompressed TO DISK='C:DbTestTestUncompressedBackupUnCompressed.bak' 
GO
BACKUP DATABASE TestCompressed TO DISK='C:DbTestTestCompressedBackupCompressed.bak' 
WITH  COMPRESSION
GO


BACKUP DATABASE TestCompressed TO DISK='C:DbTestTestCompressedBackupUnCompressed.bak' 
GO


BACKUP DATABASE TestUncompressed TO DISK='C:DbTestTestUncompressedBackupCompressed.bak' 
WITH  COMPRESSION
GO


BACKUP DATABASE TestUncompressed TO DISK='C:DbTestTestUncompressedBackupUnCompressed.bak' 
GO

Here are the times that each backup took.
Processed 11640 pages FOR DATABASE ‘TestCompressed’, FILE ‘TestCompressed_Data’ ON FILE 1.
Processed 2 pages FOR DATABASE ‘TestCompressed’, FILE ‘TestCompressed_Log’ ON FILE 1.
BACKUP DATABASE successfully processed 11642 pages IN 5.483 seconds (16.587 MB/sec).

Processed 11640 pages FOR DATABASE ‘TestCompressed’, FILE ‘TestCompressed_Data’ ON FILE 1.
Processed 1 pages FOR DATABASE ‘TestCompressed’, FILE ‘TestCompressed_Log’ ON FILE 1.
BACKUP DATABASE successfully processed 11641 pages IN 6.461 seconds (14.076 MB/sec).

Processed 25496 pages FOR DATABASE ‘TestUncompressed’, FILE ‘TestUncompressed_Data’ ON FILE 1.
Processed 2 pages FOR DATABASE ‘TestUncompressed’, FILE ‘TestUncompressed_Log’ ON FILE 1.
BACKUP DATABASE successfully processed 25498 pages IN 9.167 seconds (21.729 MB/sec).

Processed 25496 pages FOR DATABASE ‘TestUncompressed’, FILE ‘TestUncompressed_Data’ ON FILE 1.
Processed 1 pages FOR DATABASE ‘TestUncompressed’, FILE ‘TestUncompressed_Log’ ON FILE 1.
BACKUP DATABASE successfully processed 25497 pages IN 14.243 seconds (13.985 MB/sec).

Here is also a chart showing you the backup times in seconds.

1 = TestCompressedBackupCompressed.bak
2 = TestCompressedBackupUnCompressed.bak
3 = TestUncompressedBackupCompressed.bak
4 = TestUncompressedBackupUnCompressed.bak

Below is an image showing the sizes of the backups. As you can see, the compressed backup is only a third of the size of the uncompressed backup from the database that had the uncompressed table. When using a compressed backup against a table that was already using data compression the size of the backup drops from 90MB to 60MB. in this case the size is 66% of the uncompressed size.

Restores

Now let’s do the restores.

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
USE master 
GO
 
RESTORE DATABASE [TestCompressed] 
FROM  DISK = N'C:DbTestTestCompressedBackupCompressed.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO
 
--RESTORE DATABASE successfully processed 11642 pages in 22.601 seconds (4.024 MB/sec).
 
RESTORE DATABASE [TestCompressed] 
FROM  DISK = N'C:DbTestTestCompressedBackupUnCompressed.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO
 
--RESTORE DATABASE successfully processed 11641 pages in 23.593 seconds (3.854 MB/sec).
 
 
RESTORE DATABASE [TestUncompressed] 
FROM  DISK = N'C:DbTestTestUncompressedBackupCompressed.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO
--RESTORE DATABASE successfully processed 25498 pages in 29.337 seconds (6.789 MB/sec)
 
RESTORE DATABASE [TestUncompressed] 
FROM  DISK = N'C:DbTestTestUncompressedBackupUnCompressed.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO
--RESTORE DATABASE successfully processed 25497 pages in 32.358 seconds (6.155 MB/sec).
USE master 
GO

RESTORE DATABASE [TestCompressed] 
FROM  DISK = N'C:DbTestTestCompressedBackupCompressed.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

--RESTORE DATABASE successfully processed 11642 pages in 22.601 seconds (4.024 MB/sec).

RESTORE DATABASE [TestCompressed] 
FROM  DISK = N'C:DbTestTestCompressedBackupUnCompressed.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

--RESTORE DATABASE successfully processed 11641 pages in 23.593 seconds (3.854 MB/sec).


RESTORE DATABASE [TestUncompressed] 
FROM  DISK = N'C:DbTestTestUncompressedBackupCompressed.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO
--RESTORE DATABASE successfully processed 25498 pages in 29.337 seconds (6.789 MB/sec)

RESTORE DATABASE [TestUncompressed] 
FROM  DISK = N'C:DbTestTestUncompressedBackupUnCompressed.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO
--RESTORE DATABASE successfully processed 25497 pages in 32.358 seconds (6.155 MB/sec).

As you can see from the times in the comments in the code block above, the restores are faster when you use compression.

Here is a chart with the restore times in seconds.

1 = TestCompressedBackupCompressed.bak
2 = TestCompressedBackupUnCompressed.bak
3 = TestUncompressedBackupCompressed.bak
4 = TestUncompressedBackupUnCompressed.bak

Conclusion

To finish up, here is a table that shows all restore and backup times for the 4 different backups

Name  Backup time  Backup MB/sec  Restore time Restore MB/sec  File size 
TestCompressedBackupCompressed 5.483 16.587 MB/sec 22.601 4.024 MB/sec 65,646 kb
TestCompressedBackupUnCompressed 6.461 14.076 MB/sec 23.593 3.854 MB/sec 93,484 kb
TestUncompressedBackupCompressed 9.167 21.729 MB/sec 29.337 6.789 MB/sec 65,679 kb
TestUncompressedBackupUnCompressed 14.243 13.985 MB/sec 32.358 6.155 MB/sec 205,108 kb

As you can see compression is a great way to keep your backups small and it also makes your backup and restore process finish faster as well.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum