BACKUP LOG cannot be performed because there is no current database backup.

Hi,

I have a MS SQL Server 2005 Enterprise Edition 9.0.2153 which manages my BizTalk Server 2006 Databases. The BizTalk server installer automatically creates a job named Backup BizTalk Server (BizTalkMgmtDb) which should back up the databases and transaction protocolls.

The SQL Server Agent runs under domain-administrator account which has full access to my backup directory D:\backups.

The job executes the following steps:

Step 1: BackupFull:

Command:

exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, 'D:\Backups' /* location of backup files */

Step 2: MarkAndBackupLog

Command:

exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, 'D:\Backups' /* location of backup files */

My challenges are:

1. The job doesn't back up my databases(D:\Backups is empty after executing the job)

2. I get every time the following error in error protocol of the job:

BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Fehler 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Fehler 3013).

I have already switched the Databases from full to simple recovery mode and vica versa, it didn't help. Also, the above stored procedures doesn't include neither the TRUNCATE nor the LOG parameters for the logfiles so I wasn't able to solve this issue by adjusting these flags.

Any help would be appreciated.

Thanks in advance,

Greg

I think you've just got a problem with the startup sequence.

Start by putting the database in full recovery mode.

Then, trigger a full backup. I suspect that your job may not do a full on the current day, causing your problem. Log backups won't work until there is a full to bas them on. You can do a manual full backup using Management Studio, or temporarily change the schedule on your BackupFull job to perform a full backup always, and then set it back after it's run once. After that, log backups should run fine.

|||

Hi Kevin,

first of all thanks for your reply. I really appreciate it!

I've adjusted my BizTalk 2006 Database Settings and all Databases are now in full recovery mode.

BizTalkDTADb - Full

BizTalkEDIDb - Full

BizTalkMgmtDb - Full

BizTalkMsgBoxDb - Full

BizTalkRuleEngineDb - Full

SSODB - Full

I am now able - with the above mentioned job - to reguraly back up the BizTalk Server Databases except for BizTalkEDIDb.

I executed manually a full Backup on the EDI database - as you suggested - and after that I let the backupjob run. Still no backups on this database.

In the job activity monitor there is no more error regarding BACKUP LOG. That's the good news for today. A step in the right direction. There are however no traces of transactional backups in my backup directory despite setting the right parameter for the path of the transactional log backup(see my first post).

There is one more error during the execution of the job named "DTA Purge and Archive (BizTalkDTADb)":

The @.nvcFolder parameter cannot be null. If you want to only purge the database, use the dtasp_PurgeTrackingDatabase stored procedure [SQLSTATE 42000] (Fehler 50000).

My three questions therefore:

1. Why can't I backup the EDI Database?

2. Why does the backup job not create *.trn files in my backup directory?

3. How can I get rid of the "DTA Purge and Archive" job error?

Thanks in advance,

Greg

|||

Hi,

my answers to my question:

1. I was not able to back up the EDI database because its name didn't exist in the adm_OtherBackupDatabases table of the BizTalkMgmtDb database. The question is why did the SQL Server back up other databases which were not present in this table either?

2. The job created transaction backups however with the extension *.bak and not with *.trn.

3. I have let the @.nvcFolder variable on its default NULL value which was false. After I set the step parameters of the DTA Purge and Archive (BizTalkDTADb) job like follows I got rid of the error:

exec dtasp_BackupAndPurgeTrackingDatabase
0, --@.nLiveHours tinyint, --Any completed instance older than the live hours +live days
1, --@.nLiveDays tinyint = 0, --will be deleted along with all associated data
30, --@.nHardDeleteDays tinyint = 0, --all data older than this will be deleted.
'D:\Backups', --@.nvcFolder nvarchar(1024) =N'D:\Backups' , --folder for backup files
null, --@.nvcValidatingServer sysname = null,
0 --@.fForceBackup int = 0 --