Over the past couple of weeks I have been documenting the procedure I use to build new BizTalk Environments, this includes things like making sure that the backup jobs are in place and that the backup files get deleted (the default backup job shipped with BizTalk cleans up the history records in the database, but does not clean up the files created by the backup).  This week I was helping a client clean up a tracking database that had gotten very large because of a miss configured purge job, while I was working on this problem I noticed that the full backups for the tracking database via the BizTalk backup job were happening at 10:00am local time (Melbourne, Australia).  From my past experience as a SQL DBA, doing a full backup on a production database at 10:00am in the morning is probably not the best time to do it, I dismissed it as something up with the scheduler.  Today while completing the setup of a new BizTalk server for another client, I again noticed that the full backups for all the BizTalk databases were happening at 10:00am, so I though to myself, what is up with this.  The other strange thing is the file name did not match the time the file was created, here is an example:

READIFYPOC_BizTalkDTADb_Full_BTS_2007_04_12_00_00_08_497.bak - with a create date & time of  -12/04/2007 10:00 AM

So something is really up with this backup job, I opened the backup job and looked at the first step, it calls the stored procedure sp_BackupAllFull_Schedule, so I open the stored procedure and on line 29 I find this:

select  @CurrDT = getutcdate()

the picture suddenly becomes very clear, Melbourne has a UTC offset of +10 hours, now I know why the backups are running at 10:00am in the morning.

I understand why they used getutcdate(), almost all the dates in the BizTalk tracking database are stored in UTC time and when they generate reports they use something like this:

dateadd(minute, @UtcOffsetMin, [ServiceInstance/StartTime]) - from the "Most recent 100 services instances" query in HAT.

Now that I figured out why it was doing the full backups at 10:00am in the morning I started trying to find out how to fix the problem without changing the BizTalk stored procedures, I searched Google and the only thing that I found was someone that have setup a job that ran at 5 minutes before they wanted to do the full backup that executed the sp_ForceFullBackup stored procedure to force the next run of the backup job to do a full backup.  This was not the solution I was looking for, since what that would produce is a backup at the time I select and then another backup when the UTC date changed at 10:00am.  I then went and looked at the other 2 steps of the backup job, the second step that does the transaction log backups also uses getutcdate(), but the job that delete the backup history uses getdate(), so it deletes the backup history 10 hours ahead of schedule.

So as bad as I hate to change the shipped stored procedures, I decided to change them and see if that fixed the problem, YES, the problem is fixed, the full backup runs the 1st run after midnight local time and the file name matches the file create time of the file system.

I guess it is now time to open a support incident and see if they can explain why doing a full backup at 10:00am is a good thing.  If they don't I will have to create a set of _LocalTime stored procedure with my changes in them to prevent services packs from unfixing my fix.  I have already done this with my sp_DeleteBackupHistoryAndFiles stored procedure that also deletes the backup files from the file system (see the download section of my website for your copy)