Backup BizTalk Server job failed. Executed as user NT SERVICESQLSERVERAGENT. Cannot open backup device destination path.

Backup BizTalk Server job failed. Executed as user NT SERVICESQLSERVERAGENT. Cannot open backup device destination path.

Another day, another error to report directly retrieved from my backlog to be published. Today is about another error that can happen on the Backup BizTalk Server job: “Executed as user NT SERVICESQLSERVERAGENT. Cannot open backup device destination pathdatabase name“. I got this error a few months ago in a client while doing an installation assessment:

Executed as user: NT SERVICESQLSERVERAGENT. Cannot open backup device ‘C:Program FilesMicrosoft SQL ServerMSSQL13.BIZTALKMSSQLBackup<destination path>NAME_BAMPrimaryImport_Log_BTS_2017_12_18_15_07_41_447.bak’. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.). [SQLSTATE 42000] (Error 3201) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Backup BizTalk Server job: Executed as user NT SERVICESQLSERVERAGENT. Cannot open backup device destination pathdatabase name

This error was occurring on the third step of the Backup BizTalk Server job: MarkAndBackupLog.

Cause

On the third step, MarkAndBackupLog, of the Backup BizTalk Server job, if you check in input parameters of the stored procedure that is invoked there: the “sp_MarkAll” stored procedure. You will find out that the second parameter is the location of backup files and this location must exist in the file system.

The reason for this error to occur may be related for one of these reasons:

  • This step is not properly configured, and you still have “<destination path>” set as the value for the location of backup files – second parameter;
  • Or the folder/path that you define as the location of backup doesn’t exist;

Solution

You must remember that you need to ensure that all the paths specified in the BizTalk jobs must exist in the file system.

But in this case and as you will see in the error description, the backup job was not properly configured and still have “<destination path>” set as the value for the location of backup files.

To solve this problem, you need to:

  • Press the “Windows key” type “SQL Management” or “SQL” and click in “SQL Server Management Studio”.
  • In Object Explorer panel, connect to the SQL Server instance and expand the server tree.
  • Expand the “SQL Server Agent” node
  • Expand the “Jobs” node
  • Double click “Backup BizTalk Server (BizTalkMgmtDb)” to open the job properties window.
  • In the Job Properties – Backup BizTalk Server (BizTalkMgmtDb) dialog box, under “Select a page”, click “Steps”.
  • In the “Job step list”, click on the job you want to edit, in this case, “MarkAndBackupLog”, and then click “Edit”
  • On the “Command” property correctly specify a path for the backup files

Backup BizTalk Server job: Executed as user NT SERVICESQLSERVERAGENT. Cannot open backup device destination path fixed

After I properly configure the job this error was solved and I was successfully able to run the Backup BizTalk Server job.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira

Backup BizTalk Server job failed. Executed as user: NT SERVICESQLSERVERAGENT. Could not connect to server ” because ” is not defined as a remote login at the server.

Backup BizTalk Server job failed. Executed as user: NT SERVICESQLSERVERAGENT. Could not connect to server ” because ” is not defined as a remote login at the server.

In my last post, I described how can you fix the issue regarding:

Executed as user: BIZDEMOsaspereira. Could not find server ‘BIZDEMO’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. [SQLSTATE 42000] (Error 7202). The step failed.).

See more about this error here: https://blog.sandro-pereira.com/2018/05/08/backup-biztalk-server-job-failed-could-not-find-server-in-sys-servers/

And I told that this was not the only issue that you will find. The truth is that if you try to execute the Backup BizTalk Server job after you fix this last problem, the job will fail again this time with the following error:

Executed as user: NT SERVICESQLSERVERAGENT. Could not connect to server ‘BIZDEMO’ because ” is not defined as a remote login at the server. Verify that you have specified the correct login name. . [SQLSTATE 42000] (Error 18483). The step failed.

Backup BizTalk Server job failed - Could not connect to server is not defined as a remote login at the server

Cause

This error can be related to several possible problems and a common solution you will find in SQL Server forums and post is that you should Drop and re-create the linked server will resolved this problem.

However, and forgive me in advance for my SQL ignorance, I don’t have any linked Server configured, my BizTalk Server Virtual Machine is a simple standalone machine with BizTalk and SQL installed… so, it couldn’t be that problem!

Backup BizTalk Server job failed - Could not connect to server is not defined as a remote login at the server: Linked Server

So, after several tests, I started to think as a truly genuine old-school technical guy:

  • It doesn’t work? did you try to restart it?

Or as a true BizTalk developer guy:

  • Did you restart the host after your solution deployment?

And I thought that it might be necessary to restart the SQL services so that all the settings of the previous command execution have the correct effect – execution of the commands: sp_dropserver and sp_addserver necessary to fix the issue reported in the previous post.

And it was!

Solution

To fix this issue, we need to restart the SQL services

After you restart the SQL Server Services you will be able to run the Backup BizTalk Server job successfully.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira

Backup BizTalk Server job failed. Executed as user ”. Could not find server in sys.servers. Verify that the correct server name was specified

Backup BizTalk Server job failed. Executed as user ”. Could not find server in sys.servers. Verify that the correct server name was specified

After you install and configure a BizTalk Server Azure Virtual Machine, the first time you try to run the main BizTalk Server job: Backup BizTalk Server (BizTalkMgmtDb) – of course after you properly configure it – it will fail every time with the following error:

Executed as user: BIZDEMOsaspereira. Could not find server ‘BIZDEMO’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. [SQLSTATE 42000] (Error 7202). The step failed.).

BizTalk Backup Job Could not find server sys.servers

Note: will not be the only one that you will find but let’s will go step by step and we will address other errors in different blog posts

 Cause

Well, I guess (I’m sure) that this error happens because we are using a default Microsoft BizTalk Server image with all the components already installed: Visual Studio, BizTalk Server and especially SQL Server.

This problem happens because of the SQL Server name present in sys.servers table – maybe that was specified during setup of this image – is defined as “localhost” and not the actual VM name that you created on the Azure Portal and for the job work properly:

  • The SQL server name specified during setup of the Azure Virtual Machine must match the Name in sys.servers of the SQL server

To check the Name present in sys.servers table please run the following SQL Script:

SELECT name FROM sys.servers
GO

Backup BizTalk Server job Could not find server in sys.servers: check the name

Solution

To fix this issue, we need to remove the old server name and add the new server name in SQL. You can do that by:

  • Login on to SQL Server and open SSMS.
  • Connect to the instance and run below query, which will remove the old server name.
sp_dropserver 'localhost'
GO
  • And then run below query to add the new server name, that in my case was “BIZDEMO”:
sp_addserver 'BIZDEMO', local
GO

After you execute these scripts this particular problem will be solved.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira

Backup BizTalk Server (BizTalkMgmtDb) job failed with BACKUP LOG cannot be performed because there is no current database backup

Backup BizTalk Server (BizTalkMgmtDb) job failed with BACKUP LOG cannot be performed because there is no current database backup

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 BTS02

Job 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 0

Message

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.

Backup BizTalk Server (BizTalkMgmtDb) job failed BACKUP LOG cannot be performed because there is no current database backup.

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.

Fix: Backup BizTalk Server (BizTalkMgmtDb) job failed BACKUP LOG cannot be performed because there is no current database backup.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira