Trending SQL Server Tips

Database Backup Compression Feature In SQL Server 2008

By: Editor
March 25, 2009

Page: 3/3

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

How To Enable Trace Flag In SQL Server

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.

Click the Previous Page button to read this article from the beginning…

Previous Page..


Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…




Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect you privacy and you can unsubscribe at any time."