To check the
ratio of any current backup, a new field called Compressed_Backup_Size has been added to
the MSDB..BACKUPSET table. The following simple query gives you a compression ratio for
an individual backup:
SELECT backup_size/compressed_backup_size FROM msdb..backupset;
If you wish to explicitly include or exclude compression from a single backup, you
simply override the default compression setting for the instance using WITH COMPRESSION
or WITH NO_COMPRESSION in conjunction with a BACKUP DATABASE or BACKUP LOG command.
Unlike the WITH RECOVERY clause, which is the default if not specified, the default compression
setting is determined by a SQL Server instance-level setting.
There are a few caveats when it comes to SQL 2008 compression (I think I just heard
a collective sigh of relief from all of the third-party backup vendors out there):
??? You may create compressed backups only with SQL Server 2008 Enterprise Edition,
although other editions of SQL Server 2008 will be able to restore them.
??? You currently can??™t include compressed and uncompressed backups in the same
backup set.
Pages:
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606