Share via

BizTalk and compressed database backups

  • Article

Regularly backing up the BizTalk databases is an essential task, as documented here:

Backing Up and Restoring BizTalk Server Databases

“A common way to back up databases is to create a complete backup of the database once per day (if the database is not too large), then periodically make a copy of the transaction logs. This method is not suitable for use with BizTalk Server, because BizTalk may have distributed transactions pending among several databases. This is why BizTalk Server relies on transaction log marking, and the backup procedures that follow.”

Backups take up a lot of disk space and that costs money. A number of database vendors have realised this and implement compression within the backup procedures of their products to reduce the size of the resulting storage files. Last year Microsoft released such functionality in SQL Server 2008:

Backup Compression (SQL Server) 

"Backup compression was introduced in SQL Server 2008 Enterprise.

Creating compressed backups is supported only in SQL Server 2008 Enterprise and later, but beginning in SQL Server 2008, every edition can restore a compressed backup."

Does this mean that the BizTalk backup job can make use of compression? Currently, no.

Because of the use of transaction log marking, it is only supported to back up Biztalk databases using the job that is supplied with the product.

952555  How to maintain and troubleshoot BizTalk Server databases

Use the correct BizTalk SQL Server Agent jobs
"The Backup BizTalk Server job is the only supported method to back up the BizTalk Server databases."  

The backup job in BizTalk 2006 was designed to run on versions of SQL Server prior to 2008 which did not have the backup compression functionality.

The job in BizTalk 2009 will be essentially the same and has not been modifed to take advantage of the new backup compression functionality in SQL Server 2008.

Those 3rd party database products to do provide backup compression are not supported by the BizTalk backup job.

Comments

  • Anonymous
    April 24, 2009
    You can use HyperBac for compression of native BizTalk backup jobs in BizTalk 2006 because it works entirely differently to other third party SQL Server backup compression products (such as LiteSpeed)