Why use the Backup BizTalk Server job
The most important BizTalk databases are the SSODB database, the BizTalkMgmtDb database, the BizTalkMsgBoxDb database (1 or more) and the BizTalkDTADb database. The first one contains the configuration of your BizTalk Group, the BizTalkMsgBoxDb, the so-called MessageBox, contains the 'work in process' and the BizTalkDTADb, or Tracking database, contains information about the finished processes. Off course there are more BizTalk databases, but for now we just focus on the mentioned ones.
There is a lot of data exchange going on between these databases. Especially between the MessageBox and the Tracking database. And in case you have 3 MessageBoxes, you have 1 Master MessageBox which routes the 'work to be done' to the other MessageBox-databases. It's not so hard to understand, that it's important that in case of recovery these databases need to be in sync.
And there we have the reason why an ordinary backup simply isn't enough for BizTalk. An ordinary backup job makes database backups sequentially, so your backups will never be in sync. Therefore Microsoft created a mechanism which can be called the Marked Backup.
The Marked backup
By means of the 'Backup BizTalk Server' job in SQL Server, the Marked Backup writes marks to a table, so the job knows until what time it has to make a backup. These marks are actually timestamps. Since the job writes the same timestamps in all databases which need to be backed up, we can now guarantee that we have synced backups. So therefore, drop your Maintenance Plans which made the sequential backups and start using the Backup BizTalk Server job.
Since there are enough articles about setting up the Backup BizTalk Server job, I'm not going to describe it here. Instead I want to show you a bit more about how the job does its work.
If you want to set up the Backup BizTalk Server job, check out this MSDN article:
http://msdn.microsoft.com/en-us/library/aa561125(v=bts.20).aspx
NOTE: There are some small differences between the Backup jobs of the different BizTalk versions. This article is based on the job in BizTalk 2010
Inside the Backup BizTalk Server job
You'll find this job at the server which contains the BizTalkMgmtDb.
When you open the job and go to the Steps page, it looks like below.
Steps of the Backup BizTalk Server job
The job has 4 steps, namely:
- Set Compression Option
- BackupFull
- MarkAndBackupLog
- Clear Backup History
Step 1: Set Compression Option
In this step you can define if you want the backup to be compressed or not. The following command line is executed:
exec [dbo].[sp_SetBackupCompression] @bCompression = 0
This Stored Procedure writes a 0 or a 1 to the field UseCompression in the table adm_BackupSettings. The following steps of the job check this value and make the backup compressed or not as desired.
NOTE: Compression does not work on SQL Server 2005 and earlier.
Step 2: BackupFull
In step 2 the Stored Procedure sp_BackupAllFull_Schedule is executed. The command line is as follows:
exec [dbo].[sp_BackupAllFull_Schedule] 'd', 'BTS', '<destination path>'
As you can see, the Stored Procedure is executed with 3 parameters, but actually there are 6 parameters, which are all listed below.
- Frequency
- (Mark) Name
- Location
- Force Full Backup After Partial Set Failure
- Backup Hour
- Use Local Time
Frequency
With this parameter you define the frequency of a Full Backup. Values can be:
- 'h' or 'H' for hour
- 'd' or 'D' for day (this is the default value)
- 'w' or 'W' for week
- 'm' or 'M' for month
- 'y' or 'Y' for year
(Mark) Name
This value becomes written as part of the mark. All databases which are being backed up by the Backup BizTalk Server job, have a table called MarkLog.
With the Full Backup the Mark Name is only used as part of the file name of the backup.
Location
This is the location where the Full backup will be written. UNC names are allowed here. Take care, off course, that you have enough disk space available.
Force Full Backup After Partial Set Failure
The default value for this optional parameter is 0, but if you set it to 1, the job will check if the most recent backup was incomplete. If this is the case, a Full Backup will be made.
Backup Hour
In case you make hourly Full Backups, this optional parameter is used to set the hour in which that Full Backup will be made.
Use Local Time
This last parameter is also optional. With this one you can set if the backup hour is hold against UTC time or local time.
Executing sp_BackupAllFull_Schedule
When the command is executed, it performs the following checks to determine if a Full Backup has to be made:
- Check if a forced full backup is requested
- Check if an uncompleted backup exists
- Check if a backup has to be made, given the defined frequency
If one of this checks is positive, a Full Backup will be made. Any failures in this step will be returned to the SQL Server Job, by means of raising an error and returning -1.
Check if a forced full backup is requested
If needed, you can force a Full Backup. Therefore you must execute the following Stored Procedure:
sp_ForceFullBackup
It can be found in the BizTalkMgmtDb. This Stored Procedure writes the value 1 in the field ForceFull of the table adm_ForceFullBackup (this table only has one row). So when the Backup job does the Force Full Backup check and it finds a 1, a Full Backup will be made. After the forced Full Backup has been made, the bit which determines this will be reset to 0.
Check if an uncompleted backup exists
Next the Stored procedure checks whether the most recent backup was completed. For this step the procedure checks the value of the field SetComplete of the most recent record (or actually the Full Backup) in the adm_BackupHistory table.
NOTE: It only does this check when in the call to sp_BackupAllFull_Schedule, the value for Force Full Backup After Partial Set Failure was set to 1. Out-of-the-box this parameter is absent, so if you want to perform this check, you'll have to change the parameters of the call to sp_BackupAllFull_Schedule.
Check if a full backup has to be made, given the defined frequency
The last check deals with the given frequency. This check is also done against the adm_BackupHistory table. If the Date/Time of the most recent Full backup matches the given frequency of the current Date/Time, no Full Backup has to be made.
The sp_BackupAllFull Stored Procedure
Now the Stored Procedure sp_BackupAllFull is executed. The command line looks like this:
exec @ret = [dbo].[sp_BackupAllFull] @MarkName, @BackupPath, @CurrDT, @UseLocalTime
It has the following parameters:
- @MarkName: the Name of the mark as it was defined for the Stored Procedure sp_BackupAllFull_Schedule
- @BackupPath: the Location of the backup as it was defined for the Stored Procedure sp_BackupAllFull_Schedule
- @CurrDT: the current Date/Time calculated with the use of the Use Local Time parameter (UTC Time or Local Time)
- @UseLocalTime: set if the backup hour is hold against UTC time or local time as it was defined for the Stored Procedure sp_BackupAllFull_Schedule
The first steps of the Stored procedure are retrieving a new BackupSetId and a Full Mark Name.
For retrieving a new BackupSetId the Stored Procedure sp_GetNextBackupSetId is called. The Full Mark Name is created by the Stored Procedure sp_BuildFullMarkName, which has the @MarkName and the @CurrDT as parameters. This Stored Procedure also replaces any unwanted characters, which might be given in the @MarkName parameter.
Next the Stored Procedure sp_AcquireBackupWriterLock places a lock for the backup. It then retrieves the databases which are in the Backup. These databases can be found in the view admv_BackupDatabases. This view contains the names and locations off all the databases which are going to be backed up. The view retrieves it information from different locations, namely:
- the name of the current database and the server where it resides, actually the BizTalkMgmtDb
- the table adm_Group in the BizTalkMgmtDb to retrieve
- the Tracking database
- the BAM database
- the BRE database
- the table adm_MessageBox for the messageBox database(s)
- the table adm_OtherBackupDatabases for any custom databases
After checking if we are linked to the server where the to be backed up database resides, some Dynamic SQL is created to perform the backup. Since this Dynamic SQL is executed on the server/database which will be backed up, each database actually makes a backup of itself. Therefore the following Stored Procedure is executed:
exec '[' + @BackupServer + '].[' + @BackupDB + '].[dbo].sp_BackupBizTalkFull @seq=@FullMarkName, @path=@BackupPath, @BackupLocation=@backup_loc output
The parameters are:
- @BackupServer: name of the server where the to be backup database resides
- @BackupDB: name of the database which will be backed up
- @seq: contains the FullMarkName which is being set
- @path: contains the path to the location where the backup will be written
- @BackupLocation: contains the full path of the backup file (out)
The last step before creating the Full Backup, is filling the variable @BackupLocation with the full path to the backup location and the file name of the backup. This is based on:
- the @path variable as it was given as a parameter to this Stored Procedure
- the current server name a since we are now on the server that contains the to be backup up database
- the current database name since we are now on the database that contains the to be backup up database
- the text 'Full' to state that this is a Full Backup
- the @seq variable which contains the Full Mark Name
- the extension .bak
And there it is! the Full Backup statement:
Backup Database @DBName to DISK=@BackupLocation>
The parameters are:
- @DBName: name of the database which will be backed up
- @BackupLocation: the full path of the backup file
Whether or not the Full Backup has been made succesfully, the Stored Procedure sp_BackupAllFull writes a record to the adm_BackUpHistory for every database that has been backed up.
This records contains:
- the created BackupSetID
- the backed up database
- the backed up server
- the file name
- the location of the backup
- the text 'db' to state that this was a Full Backup
- the variable @timestamp as it was passed from the Stored Procedure sp_BackupAllFull_Schedule
If the backup was made succesfully, the field SetComplete in the table adm_BackupHistory (for the record of this backup) is set to 1, stating that the backup was made succesfully.
So far for the Full Backup.
Step 3: MarkAndBackupLog
The third step of the Backup BizTalk Server job writes marks and performs a Log Backup on all BizTalk databases (and any configured custom databases). The command line of this step is this one:
exec [dbo].[sp_MarkAll] 'BTS', '<destination path>'
It has the following parameters:
- Log Mark Name
- Location of the backup files
Since these parameters have the same function as with the Full Backup, it won't describe their functions in more detail here.
Executing the sp_MarkAll Stored Procedure
After initializing a bunch of variables, the Stored Procedure retrieves the current UTC time and retrieves a new BackupSetId. Both values are stored in variables as well. Next a Full Mark Name is constructed. This is done by the Stored Procedure sp_BuildFullMarkName. Then the Stored Procedure sp_AcquireBackupWriterLock places a lock for the backup.
Executing the sp_MarkBTSLogs Stored Procedure
Now the Stored Procedure sp_MarkBTSLogs is called to mark the logs.
sp_MarkBTSLogs @FullMarkName
The @FullMarkName parameter contains the Mark as it was parameterized by the Stored Procedure sp_MarkAll
To write the marks, Dynamic SQL is created and executed upon each to be backup up database, enabling these databases, to write their own marks in the MarkLog table. The marks are written by the Stored Procedure sp_SetMark or sp_SetMarkRemote. The difference between these two is that the latter Stored Procedure uses the WITH MARK keyword. This is needed when the calling procedure (sp_MarkAll) has specified WITH MARK, that procedure resides on a different server and this is the first procedure on the server. All subsequent calls should use sp_SetMark.
The following step is to check whether the backup must be compressed or not. After that a Dynamic SQL statement is created which will lead to the statement to make the Log Backup.
Compression or not
In case compression is on (don’t forget: not on SQL Server 2005 and before) and the database is not the SSODB, the following statement is executed:
exec '[' + @BackupServer + '].[' + @BackupDB + '].[dbo].[sp_BackupBizTalkLog]' @seq=@FullMarkName, @path=@BackupPath, @BackupLocation=@backup_loc output, @bCompression=@UseCompression
otherwise, this statement is executed:
exec '[' + @BackupServer + '].[' + @BackupDB + '].[dbo].[sp_BackupBizTalkLog]' @seq=@FullMarkName, @path=@BackupPath, @BackupLocation=@backup_loc output, @bCompression=@UseCompression
The parameters are:
- @BackupServer: name of the server where the to be backup database resides
- @BackupDB: name of the database which will be backed up
- @seq: contains the FullMarkName which is being set
- @path: contains the path to the location where the backup will be written
- @BackupLocation: contains the full path of the backup file (out)
- @bCompression: will the database backup be compressed or not
In the Stored Procedure sp_BackupBizTalkLog, a statement is created to execute the Stored Procedure sp_BackupBizTalk. The statement looks like this:
exec sp_BackupBizTalk @DBName, @BackupLocation, @bCompression, N'LOG'
The parameters are:
- @DBName: Name of the database which will be backed up
- @BackupLocation: The file location where the backup will be written
- @bCompression: Whether or not to compress the backup
- N'LOG': States that a Log Backup has to be made.
In sp_BackupBizTalk the Log Backup statement is created and executed.
In case of SQL Server 2005 (which doesn't support the Compression option, the statement look as follows:
exec ('Backup ' + @BackupType + ' ' + @DBName + ' to DISK=N''' + @BackupLocation + '''')
In case you have SQL Server 2008 or later and compression is on, this statement is executed:
exec ('Backup ' + @BackupType + ' ' + @DBName + ' to DISK=N''' + @BackupLocation + ''' WITH NO_COMPRESSION')
In case you have SQL Server 2008 or later and compression is off (which is by default), this statement is executed:
exec Backup ' + @BackupType + ' ' + @DBName + ' to DISK=N''' + @BackupLocation + ''' WITH COMPRESSION
If the backup was made successfully, the field SetComplete in the table adm_BackupHistory (for the record of this backup) is set to 1, stating that the backup was made successfully.
Step 4: Clear Backup History
The final step of the Backup BizTalk Server job is deleting Backup History. The command line for this step is:
exec [dbo].[sp_DeleteBackupHistory] @DaysToKeep=14
The @DaysToKeep parameter states how many days of the Backup History should be kept, where 14 is the default value. The Backup History is stored in the BizTalkMgmtDb in the table adm_BackupHistory.
Executing sp_DeleteBackupHistory
This Stored Procedure only deletes history when a more recent Full Backup still exists and only full sets (Full backups of all databases and their Log backups) will be deleted.
Cleaning up the MarkLog table
Although the Backup History is deleted, there is one table which is not cleaned up, namely the MarkLog table, which appears in each database which is backed up by the Backup BizTalk Server job. The only way to clean this table up, is by using the Terminator tool.
It can be downloaded here:
