SQL Server 2008 - BACKUP WITH COMPRESSION

It looks really good. I tested it and its really gives me 3-4 times better performance and less size than Normal / NoCompressed Backup

Compressed Backup

BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabaseCompression.BAK' WITH COMPRESSION ,INIT
--BACKUP DATABASE successfully processed 172926 pages in 40.806 seconds (34.715 MB/sec).

Not Compressed Backup

BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabaseNoCompression.BAK'
BACKUP DATABASE successfully processed 172926 pages in 111.007 seconds (12.761 MB/sec).

See the time taken; its almost 1/3 of NoCompression Backup. The size of the backup files Compressed is 197 MB and Uncompressed is 1386 MB.


So next question is , is there any difference in restoring from these two backups. How the compression is affected Restoration? So here we go…

Normal / NOCOMPRESSED Database Backup Restoration Result

RESTORE DATABASE successfully processed 172927 pages in 245.748 seconds (5.764 MB/sec).

COMPRESSED Database Backup Restoration Result

RESTORE DATABASE successfully processed 172927 pages in 199.832 seconds (7.089 MB/sec).

See the time each one took. The time taken in restoration from Compressed Backup took less time . So it seems to be a very good enhancement.

By default this property is Off. Ie. Backup are No Compression. You can set this server property true to make By default all backups are compressed. See Books Online Backup Database page for more details

I would love to use this. But if this feature only available in Enterprise Edition then? That we needs to wait and see.