It looks like database backup compression in Sql Server 2008 will only be available in Enterprise Edition, at least that is what BOL says, in the latest Sql Server 2008 CTP (November, 2007).

So I gave it a little spin. I detached AdventureWorks, attached it on a Katmai instance and run two sets of backups, one with compression and one without. Compression looks pretty decent. Backup without compression is about 135253 KB, the compressed version is only 34871 KB.

Below are the details.

backup database adventureworks to disk = ‘c:\aw_NoCompression.bak’ with no_compression

Without backup compression
(local)\sql2008(sa):
Processed 16832 pages for database ‘adventureworks’, file ‘AdventureWorks_Data’ on file 1.
(local)\sql2008(sa):
Processed 3 pages for database ‘adventureworks’, file ‘AdventureWorks_Log’ on file 1.
(local)\sql2008(sa):
BACKUP DATABASE successfully processed 16835 pages in 13.815 seconds (9.982 MB/sec).

Backup file size is 135253 KB

backup database adventureworks to disk = ‘c:\aw_Compression.bak’ with compression With backup compression

(local)\sql2008(sa):
Processed 16832 pages for database ‘adventureworks’, file ‘AdventureWorks_Data’ on file 1.
(local)\sql2008(sa):
Processed 2 pages for database ‘adventureworks’, file ‘AdventureWorks_Log’ on file 1.
(local)\sql2008(sa):
BACKUP DATABASE successfully processed 16834 pages in 8.703 seconds (15.845 MB/sec).

Backup file size is 34871 KB

Update: funny how great minds think alike 🙂 Tibor Karaszi had a similar entry today.