Tuesday 31 March 2009

SQL 2008 : Backup Compression

A little adventure in testing SQL 2008 Backup Compression.

Firstly, backing up a 10GB database normally -

BACKUP DATABASE [Track] TO  DISK = N'd:\backuptemp\TrackBAK'
WITH NOFORMAT, NOINIT,  NAME = N'Track-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

It took 3m 25s >

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 1276080 pages for database 'Track', file 'Track' on file 1.
100 percent processed.
Processed 1 pages for database 'Track', file 'Track_log' on file 1.
BACKUP DATABASE successfully processed 1276081 pages in 205.729 seconds (48.458 MB/sec).

Now, I'll backup the same database, but this time WITH COMPRESSION -
BACKUP DATABASE [Track] TO  DISK = N'd:\backuptemp\TrackCompressed.BAK'
WITH NOFORMAT, NOINIT,  NAME = N'Track-Compressed Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

It took 1m 34s >

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 1276080 pages for database 'Track', file 'Track' on file 1.
100 percent processed.
Processed 1 pages for database 'Track', file 'Track_log' on file 1.
BACKUP DATABASE successfully processed 1276081 pages in 94.380 seconds (105.630 MB/sec).


Overall SQL 2008 achieved compressing the original to 29% of the full backup's size.
Full Backup : 10,209,369 KB
Compressed backup : 2,977,141 KB



No comments: