Recently, I was writing a new article BizTalk Server Tips and Tricks: How to Backup (other) BizTalk Custom Databases (that will be released soon as a guest post in BizTalk360 Blog) that explain you how you can configure the Backup BizTalk Server (BizTalkMgmtDb) job to back up additional BizTalk custom databases (RosettaNet, ESB Toolkit, …), when I got an error message on the job saying: BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Error 4214). The full error was this:
Date 6/5/2017 2:30:00 PM
Log Job History (Backup BizTalk Server (BizTalkMgmtDb))Step ID 3
Server BTS02Job Name Backup BizTalk Server (BizTalkMgmtDb)
Step Name MarkAndBackupLog
Duration 00:00:01
Sql Severity 16
Sql Message ID 3014
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0Message
Executed as user: domain/username. Processed 1 pages for database ‘BAMPrimaryImport’, file ‘BAMPrimaryImport_log’ on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 1 pages in 0.037 seconds (0.118 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 12 pages for database ‘BizTalkDTADb’, file ‘BizTalkDTADb_log’ on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 12 pages in 0.051 seconds (1.790 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 9 pages for database ‘BizTalkMgmtDb’, file ‘BizTalkMgmtDb_log’ on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 9 pages in 0.054 seconds (1.283 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 67 pages for database ‘BizTalkMsgBoxDb’, file ‘BizTalkMsgBoxDb_log’ on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 67 pages in 0.101 seconds (5.129 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 1 pages for database ‘BizTalkRuleEngineDb’, file ‘BizTalkRuleEngineDb_log’ on file 1. [SQLSTATE 01000] (Message 4035) 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) BACKUP LOG successfully processed 1 pages in 0.051 seconds (0.086 MB/sec). [SQLSTATE 01000] (Error 3014). The step failed.
📝 One-Minute Brief
A troubleshooting guide that explains why the Backup BizTalk Server SQL job fails during the log backup step and how to fix it. The post walks through the root cause, typical misconfigurations, and the exact correction needed to restore reliable BizTalk database backups.
Cause
If you pay attention to the error message, you will notice that the job failed in the third task, MarkAndBackupLog, which is responsible for backing up the BizTalk Server database logs.
You need to remember that by default Backup BizTalk Server (BizTalkMgmtDb) job only makes a full backup of the databases once a day and every 15 minutes a backup of the databases logs. However, to be able to perform a back up of the log from a particular database, it needs that a previous full backup has been performed and register in the “adm_BackupHistory” table in the BizTalkMgmtDb database. You can validate by executing the following script:
USE [BizTalkMgmtDb]
SELECT DISTINCT [DatabaseName]
FROM [BizTalkMgmtDb].[dbo].[adm_BackupHistory]
In my case, I was configuring an additional database to be backed up via the Backup BizTalk Server (BizTalkMgmtDb) job, and at that time, the full backup of the BizTalk Databases had already occurred.
Solution
To fix the problem of BACKUP LOG cannot be performed by the Backup BizTalk Server job, you need to:
- Execute the sp_ForceFullBackup stored procedure present in the BizTalkMgmtDb database.
USE [BizTalkMgmtDb]
EXEC sp_ForceFullBackup
The next time you run the Backup BizTalk Server job, it will back up all BizTalk databases including all your BizTalk custom databases and it will be able them to execute the third task: MarkAndBackupLog, and therefore execute successfully the job once again.
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.
