BizTalk SQL Agent Jobs – Configuration Errors

In a previous post, I talked about how to configure the BizTalk SQL Agent jobs responsible for backing up the databases and purging the tracking data. These jobs are not configured by default, but their operation is essential for keeping your BizTalk system running smoothly.

Today, I inherited a developer VM at a new client with a warning that it was “running a bit slow…”.  Upon investigation, I discovered that the Backup BizTalk Server job had not been configured, so I promptly set about doing that task. However, I uncovered an error once I tried to run the job the first time:

Executed as user: NT AUTHORITY\SYSTEM. The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. [SQLSTATE 42000] (Error 4208)  BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

Hmmm… so the databases were not configured in the manner that Microsoft requires. Because the BizTalk backup jobs use log marking, FULL is the only supported recovery option. Okay, well this was easy enough to fix once I looked up the proper syntax.

First step was to check which databases were scheduled for backup. You can do this by querying the backup history table:

USE [BizTalkMgmtDb]

SELECT DISTINCT [DatabaseName]
FROM [BizTalkMgmtDb].[dbo].[adm_BackupHistory]

Now if no backup has previously been performed, you might not see anything in this table. In that case, you can pretty much count on the following databases being included with any basic BizTalk installation:

• BizTalkMgmtDb
• BizTalkMsgBoxDb
• BizTalkDTADb
• BizTalkRulesEngineDb
• SSODB

Next step is to determine which of these databases are currently configured in SIMPLE recovery mode. That can be revealed by executing the following stored proc against each database name retrieved above:

EXEC sp_helpdb ‘SSODB’

The information you want is in the ‘status’ column:

Status=ONLINE, Updateability=READ_WRITE, …, Recovery=SIMPLE, Version=…

Now you just need to run the following commands to change the recovery mode from SIMPLE to FULL for each of the databases identified in the previous step:

ALTER DATABASE BizTalkMsgBoxDb
SET recovery FULL

ALTER DATABASE BizTalkRuleEngineDb
SET recovery FULL

ALTER DATABASE SSODB
SET recovery FULL

ALTER DATABASE BAMPrimaryImport
SET recovery FULL

Now all should be good, right? Well… maybe not yet. This is the error that I got next:

Executed as user: NT AUTHORITY\SYSTEM. BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Error 4214)  BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

What the…?  But an initial backup is exactly what I want to do. Why is it complaining that there is no previous backup? It turns out that the previous failed attempt has only performed a partial backup, whereas a full backup is required prior to performing the “MarkAndBackupLog” step. So what you need to do is force a full backup by running this command (thanks to Lex Hegt and his very helpful blog post for this info):

USE [BizTalkMgmtDb]

EXEC sp_ForceFullBackup

Now your backup job should run successfully – although the above command doesn’t actually initiate the backup, it just creates a setting in the BizTalkMgmtDb that will trigger a full backup the next time it runs. You can either wait until the next scheduled backup (which by default is every 15 minutes), or kick off one manually by:

1. Right-clicking the Backup BizTalk Server job
2. Selecting “Start job at step…”
3. Clicking the Start button on the Start Job on <ServerName> window

Hopefully, you’ll soon see green checks against both steps!