After writing my last post, I remembered that in the past I got a related error to this problem, but with a different cause and solution. So, I decided to emulate the error in my lab environment and write a second post on this topic.
I remember that on occasion, in order to be faster, I copied all the job configuration text from my blog and pasted in the BackupFull step job window. However, when I try to ru,n I get a similar error:
SQL Server Scheduled Job ‘Backup BizTalk Server (BizTalkMgmtDb)’ (0x4020953E49DEFA43B8FEC18D6AD9B062) – Status: Failed – Invoked on: 2013-11-11 17:45:00 – Message: The job failed. The Job was invoked by Schedule 9 (MarkAndBackupLogSched). The last step to run was step 2 (BackupFull).
Again, the error message doesn’t help us much, so what you should do is go to the Backup BizTalk Server job history:
- Right-click on “Backup BizTalk Server (BizTalkMgmtDb)” job and select “View History” option
And try to see if you can obtain more information about the error.
Unlike the event viewer and the error that I put in the previous post, in this case, the log history shows much more information:
Date 11/11/2013 17:45:00
Log Job History (Backup BizTalk Server (BizTalkMgmtDb))
Step ID 2
Job Name Backup BizTalk Server (BizTalkMgmtDb)
Step Name BackupFull
Message
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.
📝 One-Minute Brief
Troubleshooting a misleading failure in the “Backup BizTalk Server” SQL job where the job is reported as failed even though the Full Backup (Step 2) succeeded. The actual failure often occurs in Step 3 (MarkAndBackupLog). This is frequently caused by a syntax error (incorrect quotation marks) in the sp_MarkAll stored procedure parameters or a missing physical directory for the log backups.
Cause
The copy of the text from my blog brought a wrong ASCII format, transforming the single quotation mark character into an accent (´).
All the parameters 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 are between single quotation marks.
Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son.
