Share via

BizTalk Server: Backup BizTalk Server job failed Troubleshooting

  • Article

Introduction

This article has its origin in a question posted in BizTalk Server forums: Backup BizTalk Server job failed and will provide you with common problems, possible solutions, related solutions and how can you troubleshoot this problems.

The "Backup BizTalk Server" or "Backup BizTalk Server (BizTalkMgmtDb)" is a job provided by Microsoft to do a best practice backup of the BizTalk databases. This job has to be configured for it to be able to run and will consists in four steps:

  1. Step 1 – Set Compression Option – Enable or disable compression during backup.
  2. Step 2 – BackupFull – Performs full database backups of the BizTalk Server databases.
  3. Step 3 – MarkAndBackUpLog – Backs up the BizTalk Server database logs.
  4. Step 4 – Clear Backup History – Specifies for how long the backup history is kept.

You can find more about BizTalk Server Jobs in the following TechNet Wiki article BizTalk Server 2010: SQL Agent Jobs by Tord G.Nordahl

Question/Problem

In my production environment the "Backup BizTalk Server" job is not running. When i tried to start it shows the below error :

Message "The Job failed. The job was invoked by User domain\user. The last step to run was step2 (Backup Full). The job was requested to start at step 1(Set compression option)"

Please help me in solving this issue.

You can see forum question and the whole answers history here.

Possible Causes

Unfortunately this error can happen for several reasons and we need to check what the best option for us is:

Diagnostic Steps

The generic failed message of the Backup BizTalk Server job doesn’t help us much. Fortunately there are two main places that you can look to try so see if you can obtain more information’s:

  • In the Backup BizTalk Server job history
    • Right click in “Backup BizTalk Server (BizTalkMgmtDb)” job and select “View History” option.

  • In the event viewer of the SQL Server Machine (most of the times you can find useful information there).

You can check for additional information's in the following tables:

  • “adm_BackupSettings” : where you can find information about compression setting and to force a full backup

  • “adm_BackupHistory”: where you can find the history of the most recent or actually Full Backups made.

Known Errors and Solutions

Error: The last step to run was step 2 (BackupFull). The job was requested to start at step 1 (Set Compression Option)

After running the Backup BizTalk Server you got the following error:

*Log Job History (Backup BizTalk Server (BizTalkMgmtDb))
Step ID 0
Job Name Backup BizTalk Server (BizTalkMgmtDb)
Step Name (Job outcome)
Message
The job failed. The Job was invoked by User domain\user. The last step to run was step 2 (BackupFull). The job was requested to start at step 1 (Set Compression Option).
*

By checking the Job History or in the event viewer you sometimes can find this problem:

*BackupDiskFile::CreateMedia: Backup device ‘K:\Backups\BizTalk Database\Full\BTS_BAMAlertsApplication_Full_BTS_2013_11_11_16_45_25_870.bak’ failed to create. Operating system error 3(The system cannot find the path specified.).
*

Cause

The BackupFull step is responsible for performing a full backup of the database. This step execute the “sp_BackupAllFull_Schedule” stored procedure that accepts three mandatory parameters:

  • Frequency: The default is d (daily). This is the recommended setting. Other values include h (hourly), w (weekly), m (monthly), or y (yearly).
  • MarkName: The default is BTS. The name is used as part of the backup file name.
  • BackupPath: Replace ‘<destination path>’ with the full path (the path must include the single quotes) to the computer and folder where you want to back up the BizTalk Server databases.

And three optional parameters:

  • ForceFullBackupAfterPartialSetFailure: The default is 0 when not specified, which means that if a log backup fails, no full backups are done until the next full backup frequency interval is reached. Replace with 1 if you want a full backup to be made whenever a log backup failure occurs.
  • BackupHour: The default is NULL when not specified, which means that backup job will not be associated with the time zone of the BizTalk Server computer and will run at midnight UTC time (0000). If you want to backup to run at a particular hour in the time zone of the BizTalk Server computer, specify an integer value from 0 (midnight) to 23 (11 PM) as the local time hour for the BackupHour parameter.
  • UseLocalTime: This is an extra parameter that you can also add that tells the procedure to use local time

The problem here is that you need to ensure that the path specified in the BackupPath parameter must exist in the file system.

Solution

You must remember that you need to ensure that all the paths specified in the BizTalk jobs must exist in the file system. In this case, we create the folder on the file system or modify to a valid location and then run the job again.

Original source by Sandro Pereira: Backup BizTalk Server job failed: The last step to run was step 2 (BackupFull). The job was requested to start at step 1 (Set Compression Option)

Error: Backup BizTalk Server job failed: The job failed. The Job was invoked by Schedule 9 (MarkAndBackupLogSched). The last step to run was step 2 (BackupFull).

After running the Backup BizTalk Server you got the following error:

*Log Job History (Backup BizTalk Server (BizTalkMgmtDb))
Job Name Backup BizTalk Server (BizTalkMgmtDb)
Step Name (Job outcome)
Message
The job failed. The Job was invoked by Schedule 9 (MarkAndBackupLogSched). The last step to run was step 2 (BackupFull).
*

By checking the Job History or in the event viewer you sometimes can find this problem:

*Executed as user: NT SERVICE\SQLSERVERAGENT. Incorrect syntax near ‘´’. [SQLSTATE 42000] (Error 102) Unclosed quotation mark after the character string ‘ /* location of backup files */ ‘. [SQLSTATE 42000] (Error 105). The step failed.
*

Cause

By copy and pasting you brought a wrong ASCII format transforming the single quotation mark character in an accent (´) or simply made a typing mistake.

All the parameter of the sp_BackupAllFull_Schedule must be placed between single quotation marks:

  • Frequency: 'd', 'w' and so on
  • Name: 'BTS'
  • Location of backup files: 'c:\backups'

Solution

Fix the job to ensure that all the parameters of the sp_BackupAllFull_Schedule is between single quotation marks.

Original source by Sandro Pereira: Backup BizTalk Server job failed: The job failed. The Job was invoked by Schedule 9 (MarkAndBackupLogSched). The last step to run was step 2 (BackupFull).

Error: Could not relay results of procedure 'sp_BackupBizTalkFull' from remote server

By checking the Job History or in the event viewer you sometimes can find this problem:

*Could not relay results of procedure 'sp_BackupBizTalkFull' from remote server ‘Server Name’. [OLE/DB provider returned message: Timeout expired]  *

Cause

Backing up the databases from the other SQL instance was taking more than 10 minutes to finish. And as we all might be aware of the fact that the other SQL instance would allow remote queries for 10 minutes only. So even before the remote query can complete, other SQL instance was stopping this query and throwing the above error message.

Solution

To avoid this situation, we need to changed the remote query timeout value on the other SQL instance and allowed it to execute remote queries without the time limit.

You can change the above setting on the SQL server by following the below steps:

  • In the object explorer of SQL server management studio, right click the server and select properties.
  • Click the Connections node.   
  • Under Remote Server Connections, in the Remote query timeout box, type 0. 0 means there is no timeout applied for the remote queries.

However the better approach to tackle this problem would be to initially change the remote query timeout value to 0. After doing this, run the query which was having this problem and take a SQL profiler trace simultaneously. Using this SQL profiler trace, you would come to know how long the query actually took to complete and you can change the property accordingly.

Original source by Yakshit Gohel: Backup BizTalk Job not able to backup databases which reside on the different SQL server

Error: The last step to run was step 3 (MarkAndBackupLog). The job was requested to start at step 1 (Set Compression Option).

After running the Backup BizTalk Server you got the following error:

Log Job History (Backup BizTalk Server (BizTalkMgmtDb))
Job Name Backup BizTalk Server (BizTalkMgmtDb)
Step Name (Job outcome)
Message
The last step to run was step 3 (MarkAndBackupLog). The job was requested to start at step 1 (Set Compression Option).

By checking the Job History or in the event viewer you sometimes can find this problem:

*Executed as user: NT SERVICE\SQLSERVERAGENT. Incorrect syntax near ‘´’. [SQLSTATE 42000] (Error 102) Unclosed quotation mark after the character string ‘ /* location of backup files */ ‘. [SQLSTATE 42000] (Error 105). The step failed.
*

Cause

The MarkAndBackupLog step is responsible for marking the logs for backup, and then backing them up. This step execute the “sp_MarkAll” stored procedure that accepts two mandatory parameters:

  •     @MarkName: Log mark name is part of the naming convention for backup files
  •     @BackupPath:. You must change the destination path this to a valid one. It may be local or a UNC path to another server

And one optional parameter:

  •     @UseLocalTime: This is an extra parameter that you can also add that tells the procedure to use local time

The mandatory parameters (MarkName and BackupPath) must be placed between single quotation marks:

  •     MarkName: 'BTS', 'BTS2013LAB01' and so on
  •     BackupPath: 'c:\backups'

The problem here is that for some reason you are not using a single quotation mark.

Original source by Sandro Pereira: Backup BizTalk Server job failed: The job failed. The Job was invoked by User domain\user. The last step to run was step 3 (MarkAndBackupLog). The job was requested to start at step 1 (Set Compression Option).

Solution

Fix the job to ensure that all the parameters of the sp_MarkAll is between single quotation marks.

Error: The last step to run was step 3 (MarkAndBackupLog). The job was requested to start at step 1 (Set Compression Option). (Part II)

By checking the Job History or in the event viewer you sometimes can find this problem:

*Log Job History (Backup BizTalk Server (BizTalkMgmtDb))
Step ID 3
Job Name Backup BizTalk Server (BizTalkMgmtDb)
Step Name MarkAndBackupLog
Sql Severity 16
Sql Message ID 3013
Message
Executed as user: NT SERVICE\SQLSERVERAGENT. Cannot open backup device ‘K:\Backups\BizTalk Database\Logs\BTS2013LAB01_BAMAlertsApplication_Log_BTS_2013_11_12_17_51_07_990.bak’. Operating system error 3(The system cannot find the path specified.). [SQLSTATE 42000] (Error 3201) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
*

or

*BackupDiskFile::CreateMedia: Backup device ‘K:\Backups\BizTalk Database\Logs\BTS2013LAB01_BAMAlertsApplication_Log_BTS_2013_11_12_17_51_07_990.bak’ failed to create. Operating system error 3(The system cannot find the path specified.).
*

Cause

If you check the parameters of the “sp_MarkAll” stored procedure you will see that the second parameter is the location of backup files and this location must exist in the file system.

Solution

You must remember that you need to ensure that all the paths specified in the BizTalk jobs must exist in the file system. In my case, we create the folder on the file system and run the job again with success.

Original source by Sandro Pereira: Backup BizTalk Server job failed: The job failed. The Job was invoked by User domain\user. The last step to run was step 3 (MarkAndBackupLog). The job was requested to start at step 1 (Set Compression Option).

Error: The "Backup BizTalk Server" job fails when backup files accumulate over time in the Microsoft BizTalk Server database server

In Microsoft BizTalk Server 2004 and later, the Backup BizTalk Server job fails, and problems occur that relate to limited disk space. This happens when the backup files accumulate over time. This fills up the disk that houses the backup files.

Cause

This issue occurs because the SQL Server Agent job that is named "Backup BizTalk Server" does not provide functionality for deleting backup files that have accumulated over time.

Solution

You can solve this problem by implementing you custom sp_DeleteBackupHistoryAndFiles. You can find more information in the  following article BizTalk Server Backup Job Does Not Delete Backup Files by Trace Young or in Microsoft Support: http://support.microsoft.com/kb/982546/en-us

See Also

Read suggested related topics:

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.