* UPDATED : Sep 15, 2008 to include HyperBac for SQL Server in the comparison results.
SQL Server 2008 now supports compression when backing up your databases. But note this is only included in the Enterprise edition which is a pity.
Note: Though creating compressed backups is supported only in SQL Server 2008 Enterprise and later, every SQL Server 2008 or later edition can restore a compressed backup.
Note: The tests was made on a low end test machine hence the actual throughput is not good but the overall results are important as percentages
In this example I will show you some metrics when dealing with SQL Server backups on SQL Server 2008 Enterprise. I am also comparing Red-Gate SQL Backup and Quest SQL LightSpeed.
I have a database I have created called Performance which is currently using 15.3GB
-- SQL Server 2008 Backup with Compression
BACKUP DATABASE [Performance] TO
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Performance_Compressed'
WITH COPY_ONLY, NOFORMAT, INIT, NAME = N'Performance-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,
COMPRESSION, STATS = 10
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 384760 pages for database 'Performance', file 'Performance' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2001' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2002' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2003' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2004' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2005' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2006' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2007' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2008' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2009' on file 1.
100 percent processed.
Processed 1 pages for database 'Performance', file 'Performance_log' on file 1.
BACKUP DATABASE successfully processed 385913 pages in 150.111 seconds (20.084 MB/sec).
-- SQL Server 2008 Backup with No Compression
BACKUP DATABASE [Performance] TO
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Performance_UnCompressed'
WITH COPY_ONLY, NOFORMAT, INIT, NAME = N'Performance-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,
NO_COMPRESSION, STATS = 10
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 384760 pages for database 'Performance', file 'Performance' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2001' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2002' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2003' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2004' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2005' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2006' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2007' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2008' on file 1.
Processed 128 pages for database 'Performance', file 'Orders2009' on file 1.
100 percent processed.
Processed 1 pages for database 'Performance', file 'Performance_log' on file 1.
BACKUP DATABASE successfully processed 385913 pages in 206.006 seconds (14.635 MB/sec).
Now the results:
Database Size as listed in SQL Server : 15,350 MB
| |
Uncompressed |
Compressed |
| Backup time |
206 Seconds |
150 Seconds |
| Backup speed |
14.635 MB/Second |
20.084 MB/second |
| File Size |
3,087,475 KB |
452,749 KB |
So as a result the compressed backup out performed the uncompressed backup as follows:
- 72.86% of the time
- 37.23% faster throughput
- 14.66% of the uncompressed file size
So how does this compare to the 3rd party tools?
Red-Gate - SQL Backup 5
Red-Gate SQL Backup 5 supports compression and encryption of 32-bit and 64-bit versions of SQL Server 2008, SQL Server 2005 and SQL Server 2000 (SP 3 or later)
| |
Uncompressed |
Compressed
Level 1 |
Compressed
Level 2 |
Compressed
Level 3 |
256-bit
Encryption
No Compression |
256-bit Encryption
Compressed Level 1 |
256-bit Encryption
Compressed Level 2 |
256-bit Encryption
Compressed Level 3 |
| Backup time |
155 Seconds |
95 Seconds |
119 Seconds |
128 Seconds |
162 Seconds |
115 Seconds |
95 Seconds |
126 Seconds |
| Backup speed |
19.42 MB/Sec |
31.68 MB/Sec |
25.29 MB/Sec |
23.51 MB/Sec |
18.58 MB/Sec |
26.17 MB/Sec |
31.68 MB/Sec |
23.88 MB/Sec |
| File Size |
3,088,078 KB |
532,149 KB |
448,821 KB |
412,893 KB |
3,088,078 KB |
532,149 KB |
448,821 KB |
412,893 KB |
* Red gate supports 3 different levels of compression and two encryption settings - listed as 128-bit key and 256-bit key.
Quest - SQL LightSpeed 5.0
Quest SQL LightSpeed supports compression and encryption of 32-bit and 64-bit versions of SQL Server 2008, SQL Server 2005, SQL Server 2000 (Service Pack 4), SQL Server 7.0 (Service Pack 4)
| |
Uncompressed
Did not WORK - still compressed |
Compressed
Level 1 |
Compressed
Level 5 |
Compressed
Level 11 |
256-bit
Encryption
No Compression |
256-bit Encryption
Compressed Level 1 |
256-bit Encryption
Compressed Level 5 |
256-bit Encryption
Compressed Level 11 |
| Backup time |
118 Seconds |
92 Seconds |
114 Seconds |
906 seconds |
115 Seconds |
119 Seconds |
116 Seconds |
709 Seconds |
| Backup speed |
25.50 MB/Sec |
32.71 MB/Sec |
26.40 MB/Sec |
3.32 MB/Sec |
26.17 MB/Sec |
25.30 MB/Sec |
25.94 MB/Sec |
4.24 MB/Sec |
| File Size |
565,343 KB |
565,456 KB |
394,567 KB |
376,114 KB |
565,399 KB |
565,379 KB |
393,634 KB |
376,114 KB |
* LightSpeed supports 11 different levels of compression and 9 encryption settings - listed as 40-bit RC2, 56-bit RC2, 112-bit RC2, 128-bit RC2,168-bit 3DES, 128-bit RC4, 128-Bit AES, 192-bit AES, 256-bit AES
Hyperbac for SQL Server
Quest SQL LightSpeed supports compression and encryption of 32-bit and 64-bit versions of SQL Server 2008, SQL Server 2005, SQL Server 2000 (Service Pack 4), SQL Server 7.0 (Service Pack 4)
| |
Uncompressed
(Custom Ext) |
Compressed
.HBC Ext |
Compressed
.ZIP Ext
(Zip) |
Compressed
.HBC2 Ext
(FastZip) |
256-bit
Encryption
No Compression
(Custom Ext) |
256-bit Encryption
Compressed
.HBE Ext |
| Backup time |
210 Seconds |
114 Seconds |
101 Seconds |
112 Seconds |
196 Seconds |
119 Seconds |
| Backup speed |
14.293 MB/Sec |
26.50 MB/Sec |
29.65 MB/Sec |
26.852 MB/Sec |
15.30 MB/Sec |
25.18 MB/Sec |
| File Size |
3,087,475 KB |
433,857 KB |
433,022 KB |
545,840 KB |
3,087,475 KB |
434,211 KB |
* Hyperbac supports 2 levels of compression and 3 encryption levels listed as AES-256, AES-192, and AES-128
Overall Fasted Backup
| |
Backup Time |
Backup Speed |
File Size |
| Quest - SQL LightSpeed |
92 Seconds |
32.71 MB/Sec |
565,456 KB |
| Red-Gate - SQL Backup 5 |
95 Seconds |
31.68 MB/Sec |
532,149 KB |
| Hyperbac for SQL Server |
101 Seconds |
29.65 MB/Sec |
433,022 KB |
| Microsoft SQL Server 2008 |
150 Seconds |
20.06 MB/Sec |
452,749 KB |
Overall Smallest Backup File
| |
Backup Time |
Backup Speed |
File Size |
| Quest - SQL LightSpeed |
906 Seconds
116 Seconds |
3.32 MB/Sec
24.94 MB/Sec |
376,114 KB
393,634 KB |
| Red-Gate |
126 Seconds |
23.88 MB/Sec |
412,893 KB |
| Hyperbac for SQL Server |
101 Seconds |
29.65 MB/Sec |
433,022 KB |
| Microsoft SQL Server 2008 |
150 Seconds |
20.08 MB/Sec |
452,749 KB |
References: