Explain about Database Backup Compression Feature In SQL Server 2008

Database Backup Compression Feature In SQL Server 2008:

Ref:
http://www.mytechmantra.com/LearnSQLServer/Backup_Compression_Feature_In_SQL_Server_2008_P1.html

Introduction

Database Backup Compression was one of the most awaited features by Database Administrators in SQL Server 2008. Currently, this feature is only available in SQL Server 2008 Enterprise and Developer Editions. Databases which are backed up using database compression feature can be restored on all SQL Server 2008 editions. By default, this feature is turned off at the SQL Server Instance level.

Configuring Database Backup Compression Feature

Database Administrators can configure database backup compression feature of SQL Server 2008 at SQL Server instance level in the below mentioned two ways.

• Configure Database Backup Compression Feature Using SQL Server Management Studio
• Configure Database Backup Compression Feature Using TSQL 

You need to be a member of serveradmin or sysadmin fixed server role to configure database backup compression at SQL Server Instance Level.

Configure Database Backup Compression Feature Using SQL Server Management Studio (SSMS)

1. 1. Once you are connected to SQL Server 2008 Instance, right click the server and select properties.
   
   2. In the Server Properties Page, click on Database Settings node which is available on the left panel.
   
   3. On the right side panel, under Backup and restore you will be able to see a check box named compress backup.
   
   
   • When compress backup checkbox is unchecked then it means database backup compression is not enabled at SQL Server Instance level.
   • When compress backup checkbox is checked then it means database backup compression is enabled at SQL Server Instance level.
   
   

Configure Database Backup Compression Feature Using TSQL

You can also configure database backup compression feature at SQL Server Instance level using the below mentioned TSQL.

USE MASTER
GO

EXEC sp_configure 'backup compression default', '1'
GO

RECONFIGURE WITH OVERRIDE
GO

In this article we will first compare the time it takes to perform a native backup (uncompressed) and the time it takes to perform a compressed backups of AdventureWorks database.

Finally, we will compare the time it takes to restore a native backup (uncompressed) and compressed backups of AdventureWorks database.

The size of AdventureWorks database which is available with SQL Server 2008 is 178.75 MB. 

Native or Uncompressed Database Backup

Use the below query to perform a native or uncompressed backup of AdventureWorks database. 

Use master
GO

BACKUP DATABASE AdventureWorks
TO DISK = N'C:\BackupCompression\AdventureWorks_Native.Bak'
WITH INIT, STATS = 25
GO

Database Name	Time Taken in Seconds	Database Backup Size (MB)
AdventureWorks	35.444	168.021

Compressed Database Backup

Use the below query to perform a compressed backup of AdventureWorks database.

Use master
GO

BACKUP DATABASE AdventureWorks
TO DISK = N'C:\BackupCompression\AdventureWorks_Compressed.Bak'
WITH COMPRESSION, INIT, STATS = 25
GO

Database Name	Time Taken in Seconds	Database Backup Size (MB)
AdventureWorks	16.328	40.259

Disk Space Used By Databases

 

Disk Space Required for SQL Server Compressed Backups

In SQL Server 2008 / 2008 R2 you could have noticed that once SQL Server starts backing up your database using Database Backup Compression Feature it will allocates approximately 1/3 Disk Space for database backup file. The backup file can be either a Full, Differential or a Transactional log backup file.

For example, let us assume that the actual size of the database is 3 GB then when you perform a full backup of the database using Database Compression Feature, SQL Server will internally allocate 1 GB of disk space initially for the database backup file, but finally you may end up having a backup file which will be less than 1 GB.

However, you can override this behavior of SQL Server by enabling Trace Flag 3042 as mentioned in the following Microsoft Knowledge Base article Space requirements for backup devices in SQL Server.

Once you enable Trace Flag 3042, SQL Server will not extend the target backup device to a pre calculated size i.e. 1/3 of the actual database size. However, SQL Server will allow the backup file grow incrementally as like how native backup works.

/* Enable Trace Flag 3042 */
DBCC TRACEON (3042,-1)
GO
/* Enabled Trace Flags */
DBCC TRACESTATUS
GO

Important: As per Microsoft Knowledge Base article Space requirements for backup devices in SQL Server if you enable trace flag 3042 you may see a negative performance effect on the database compressed backup operation especially when the database is backed up to a remote network location.

Restoring Native or Uncompressed Database Backup

Use the below query to restore AdventureWorks database from a native backup.

USE master 
GO

RESTORE DATABASE AdventureWorks_Native  
FROM DISK = N'C:\BackupCompression\AdventureWorks_Native.Bak' 
WITH 
MOVE N'AdventureWorks_Data' TO N'C:\BackupCompression\AdventureWorks_Native.mdf',       
MOVE N'AdventureWorks_Log' TO N'C:\BackupCompression\AdventureWorks_Native.ldf',      
RECOVERY,   
STATS = 10
GO

Database Name	Time Taken in Seconds
AdventureWorks_Native	79.419

Restoring Compressed Database Backup

Use the below query to restore AdventureWorks database from a compressed backup.

USE master 
GO

RESTORE DATABASE AdventureWorks_Compressed  
FROM DISK = N'C:\BackupCompression\AdventureWorks_Compressed.Bak' 
WITH 
MOVE N'AdventureWorks_Data' TO N'C:\BackupCompression\AdventureWorks_Compressed.mdf',       
MOVE N'AdventureWorks_Log' TO N'C:\BackupCompression\AdventureWorks_Compressed.ldf',      
RECOVERY,   
STATS = 10
GO

Database Name	Time Taken in Seconds
AdventureWorks_Compressed	60.701

Conclusion

In this article you have seen how to considerably reduce the duration of both backup and restore operations by utilizing database backup compression feature of SQL Server 2008.

Read more: http://www.mytechmantra.com/LearnSQLServer/Backup_Compression_Feature_In_SQL_Server_2008_P3.html#ixzz3cf0qBucb