NetBackup backup job failed with status code 2 when protecting Microsoft SQL Server availability groups and non-availability groups databases

Backups failed with status code 2 and Error: Failed to query database metadata for <Database> is shown in the job details.

This issue applies to:

• SQL Enterprise Server 2017 and later
• NetBackup 8.2 and later

From NetBackup Job details:

Apr 11, 2019 6:50:28 AM - Info nbjm (pid=7752) starting backup job (jobid=4) for client <CLIENTNAME>, policy SIP_RSAG, schedule Full

Apr 11, 2019 6:50:28 AM - Info nbjm (pid=7752) requesting MEDIA_SERVER_ONLY resources from RB for backup job (jobid=4, request id:{C95C159F-F8F9-4BFB-8CD6-5EB288F441CC})

Apr 11, 2019 6:50:28 AM - requesting resource  Disk_Stu1

Apr 11, 2019 6:50:29 AM - estimated 0 kbytes needed

Apr 11, 2019 6:50:29 AM - Info nbjm (pid=7752) started backup (backupid=<CLIENTNAME>_1554983429) job for client <CLIENTNAME>, policy SIP_RSAG, schedule Full on storage unit 

Apr 11, 2019 6:50:30 AM - started process bpbrm (pid=11212)

Apr 11, 2019 6:50:32 AM - Info bpbrm (pid=11212) <CLIENTNAME> is the host to backup data from

Apr 11, 2019 6:50:32 AM - Info bpbrm (pid=11212) reading file list for client

Apr 11, 2019 6:50:32 AM - connecting

Apr 11, 2019 6:50:33 AM - Info bpbrm (pid=11212) starting bphdb on client

Apr 11, 2019 6:50:33 AM - Info bphdb (pid=1344) Backup started

Apr 11, 2019 6:50:33 AM - connected; connect time: 0:00:00

Apr 11, 2019 6:50:35 AM - Info dbclient (pid=11528) INF - Intelligent policy initiated by server <CLIENTNAME>

Apr 11, 2019 6:50:35 AM - Info dbclient (pid=11528) INF - Intelligent policy <SIP_RSAG> is valid. The input file <D:\NBU\NetBackup\dbext\mssql\input.1344.0.0411119.065033> has been parsed successfully

Apr 11, 2019 6:50:36 AM - Info dbclient (pid=11528) INF - Instance server name resolved to: <SERVERNAME>

Apr 11, 2019 6:50:38 AM - Info dbclient (pid=11528) INF - WARNING - Failed to enumerate database files for database: <RSAGDB>.

Apr 11, 2019 6:50:38 AM - Info dbclient (pid=11528) INF - Intelligent policy <SIP_RSAG> has resulted in <1> batch operation(s).

Apr 11, 2019 6:50:39 AM - Info dbclient (pid=11528) INF - Intelligent policy <SIP_RSAG>: <1> operation(s) will protect full databases.

Apr 11, 2019 6:50:39 AM - Info dbclient (pid=11528) INF - Intelligent policy preprocessing has finished, created temporary batch file <__06_50_38_383_00.bch>, and submitted it to the backend agent for processing

Apr 11, 2019 6:50:40 AM - Info dbclient (pid=11528) INF - Error: Failed to query database metadata for <RSAGDB>

Apr 11, 2019 6:50:42 AM - Info dbclient (pid=11528) INF - Results of executing <__06_50_38_383_00.bch>: 

Apr 11, 2019 6:50:42 AM - Info dbclient (pid=11528) <0> operations succeeded. <1> operations failed.

Apr 11, 2019 6:50:42 AM - Info dbclient (pid=11528) INF - The following object(s) were not backed up successfully.

Apr 11, 2019 6:50:43 AM - Info dbclient (pid=11528) INF - RSAGDB

Apr 11, 2019 6:51:04 AM - Error bpbrm (pid=11212) from client <CLIENTNAME>: ERR - exit status: <2>

Apr 11, 2019 6:51:04 AM - Error bpbrm (pid=11212) from client <CLIENTNAME>: ERR - bphdb exit status = 2: none of the requested files were backed up

Apr 11, 2019 6:51:04 AM - Info bphdb (pid=1344) done. status: 2: none of the requested files were backed up

Apr 11, 2019 6:51:04 AM - end writing

none of the requested files were backed up  (2)

In SQL Server 2012 up to SQL Server 2016, all availability groups required a cluster for high availability and disaster recovery (HADR) operations. Read-scale availability groups, which do not use a cluster manager, were introduced with SQL Server 2017. However, read-scale availability groups (configured with Cluster Type: NONE) can still be created successfully on SQL servers that are part of a Windows Server Failover Cluster (WSFC).

The problem occurs when using the NetBackup for Microsoft SQL Server agent to protect SQL Server 2017 and later availability groups (basic, advanced, and read-scale) and stand-alone databases that are configured on a cluster. The backup job fails with status code 2 with the error Failed to query database metadata for <Database>, shown in the job details.

Also, running this T-SQL query: 

select [master].[sys].fn_hadr_backup_is_preferred_replica(N' Read-ScaleDB') as PreferredReplica

Results in this error:

Error code 5007 - Failed to obtain the Windows Server Failover Clustering (WSFC) resource handle for cluster resource with name or ID. 

Microsoft doesn’t support read-scale availability groups configured in a WSFC environment. Also in SQL Server 2019 CTP2.2, creating and configuring a read-scale availability group in a clustered environment is prohibited.

If you encounter the above issue with read-scale availability groups configured on a cluster, follow the procedures provided in the solutions section below. The NetBackup for Microsoft SQL Server agent will support the protection of read-scale availability groups with intelligent policies starting in NetBackup 8.2.

Solution 1:

Configure the read-scale workloads for an availability group without a cluster manager or in a cluster-less environment. This architecture doesn’t provide high availability. It only provides read-scale capability.

Solution 2:

If the read-scale availability groups are configured on SQL Servers that are part of a WSFC for high availability, perform the backup operations on the primary replica. This may not be an ideal situation if your business requirement is to conserve mission critical resources running on the primary replica, but can provide a workaround for the backup failure. To enable backups to run on the primary replica, follow one of the two configuration options below.

Option 1. Configure the MS-SQL-Server intelligent policy. On the Microsoft SQL Server tab, for the Availability Database Backup Preference select Protect primary replica. Apply this selection to both the Database Options and Transaction Log Options as shown below.

Option 2. In SQL Server Management Studio or with the T-SQL command, change the backup preference for the availability group to “Primary”.

2. 1 Configure the MS-SQL-Server intelligent policy. On the Microsoft SQL Server tab, for the Availability Database Backup Preference select Protect preferred replica. Apply this selection to both Database Options and Transaction Log Options as shown below.

Solution 3:

Configure the read-scale availability group as basic or advanced availability group in a WSFC environment. This architecture provides both HADR and scalability benefits.

Reference:

For further details on how to configure cluster-less read-scale AO/AG refer to this link:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-scale-availability-groups?view=sql-server-2017