Implementing New Manageability Features and Practices - Microsoft SQL Server 2008 R2 Administration - page 275

2. The previous statement's result set will show the default value for backup

compression before and after the configuration of backup compression:

Name Minimum Maximum config_value run_value
--Before backup compression configuration
backup
compression
default

0 1 0 0

--After backup compression configuration
backup
compression
default

0 1 1 1

3. Now, let us run the backup statement using both the WITH COMPRESSION and WITH

NO_COMPRESSION options.

4. Let's backup the database using the WITH COMPRESSION option first:

--BACKUP database compressed way
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'D:\SampleDBz\AdventureWorks2008R2_compressed.bak'WITH
COMPRESSION

The result set is as follows:

Processed 1232 pages for database 'AdventureWorks2008R2', file
'AdventureWorks2008R2_Data' on file 2.
Processed 1 pages for database 'AdventureWorks2008R2', file
'Adventureworks2008R2_log' on file 2.
BACKUP DATABASE successfully processed 1233 pages in 0.774 seconds
(12.445 MB/sec).

5. Now let's backup the database using the WITH NO_COMPRESSION option:

--BACKUP database uncompressed way
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'D:\SampleDBz\AdventureWorks2008R2_uncompressed.bak'
WITH NO_COMPRESSION

The result set is as follows:

Processed 1232 pages for database 'AdventureWorks2008R2', file
'AdventureWorks2008R2_Data' on file 1.
Processed 1 pages for database 'AdventureWorks2008R2', file
'Adventureworks2008R2_log' on file 1.
BACKUP DATABASE successfully processed 1233 pages in 0.698 seconds
(13.800 MB/sec).