This post was originally published here
Recently, I was writing a new article “BizTalk Server Tips and Tricks: How to Backup (other) BizTalk Custom Databases” (that will be release 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.
Cause
If you take attention to the error message, you will notice that the job failed in the third task: MarkAndBackupLog, that is responsible for performing the backup’s the BizTalk Server databases 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 each 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.
