BizTalk Backup Job Configuration, what does ‘Frequency’ mean?

The SQL job (“Backup BizTalk Server (BizTalkMgmtDb)”) responsible for taking BizTalk backup is by default scheduled to run every 15 minutes. This is done via normal SQL job scheduling process. User can change this value according to their requirement, but do you testing before changing this value. Bringing this value too low will result in backup been taken frequently and putting lot of background load in your SQL environment. There is great article by Nick Heppleston going in great depth about BizTalk backup. There is also a great MSDN article showing How to configure your BizTalk Backup Job. I’ll try to explain few parameters which are not documented  correctly and causes few confusion.

sp_BackupAllFull_Schedule – @Frequency

@Frequency:

It can have one of the following values h(hourly), d (daily), w(weekly), m(monthly), y(yearly) . This parameter basically tells the frequency at which the full backup should be taken. Note: The SQL job itself is scheduled to run every 15 minutes (default). So every time this job is run, transaction log backups are always taken, in spite of the frequency setting. There is also couple of other parameters in the stored procedure @ForceFullBackupAfterPartialSetFailure, @BackupHour, @UseLocalTime that are related to the @frequency parameter.

@ForceFullBackupAfterPartialSetFailure:

The default value for this parameter is ‘false’, which mean if for some reason full backup fails, the system will wait for the next cycle until the full backup is made. This could be dangerous if your @frequency is setting is high (something like weekly, monthly, yearly). I’ll recommend to set this flag to true, so every time there is a failure, the system will force itself to create a full backup. There will be little bit of performance impact, but it’s better to have a recoverable system.

@BackupHour:

This is directly related to @Frequency, when you set the frequency to h (hourly). You basically can set which hour of the day you want the full backup to happen. You can choose a value between 0 to 23 (representing 24 hour clock ). If left blank, the fullback will happen every hour. Also if you set it to some crazy number like 300 or -20, the system will force it to 0.

@UseLocalTime:

By default the logic is to work with UTC time, it’s better to set this to true, so that the jobs run in your local time. Example: If you live in Australia (which is UTC + 10 hours), you backup will happen at 10am rather than midnight)