Troubleshooting SQL database backup failures.

Description

Error Message

An error occurred on a query to database TESTDB - The Backup Exec SQL Agent was not used to create the last full backup of this database. You must use the SQL Agent to run a full backup before you run a differential backup or transaction log backup.

OR

Final error: 0xe0000363 - The Backup Exec SQL Agent was not used to create the last full, differential, or log backup of this database. You must use the SQL Agent to run a full backup before you can run a differential backup or transaction log backup.

Or

V-79-57344-37937 - Database was recently restored. A new Full backup is needed before incremental, differential, or transaction log backups can be run.

or
V-79-57344-867 - The last Full backup of database was not made by this application. Run a new Full backup, then run this job again.

or

Error Code: 0xFF2B1076
UMI Code: V-79-65323-4214 - An error occurred on a query to database model.
UMI Code: V-79-65323-4214 - BACKUP LOG cannot be performed because there is no current database backup.

or

UMI Code: V-79-40960-37945
Error Code: 0xA0009439 : The database is in a Microsoft SQL Server Availability Group. This server is not currently the primary node for that group. Full backups are not supported on non-primary Availability Group servers.

Cause

The Differential/Log backup of SQL database may fail with the above mentioned error when running SQL backups in Backup Exec using the Full/Differential Log backup strategy.

When any backup application is used in conjunction with Backup Exec (for example VSR or SQL internal backup utility) then it may render the SQL Server differential or log backups unrecoverable, even though the differential or log backups showed that they completed successfully in the job log.

This issue occurs because the Log Sequence Number (LSN) for SQL is reset by the other backup application. So subsequent differential or log backups performed by BE are relative to the other backup application instead of the original BE Full backup. The subsequent differential or log backups are thus rendered unrecoverable by BEWS, because it is unable to use the backup sets created by the other backup application.

Backup Exec now runs a reference check against the Microsoft SQL instance to verify if the last database backup was taken with Backup Exec SQL Agent. This change helps maintain the integrity of the SQL backups.

Some important points to be considered while performing SQL database backup:

1. A transaction log backup is not required if the SQL database recovery model is set to simple. An attempt to backup a SQL database configured in simple recovery model will fail.

2. If the SQL database is set to full recovery model, it is recommended to run transaction log backup to perform a point in time recovery. If you feel that the point in time recovery is not required or the database is not highly critical, you do not need to run a separate job to backup the transaction log.

3. To perform the SQL transaction log backup, the SQL inbuilt backup utility should be disabled. No other application should be used to backup the SQL database and transaction logs apart from Backup Exec SQL agent. Check with your SQL administrator to ensure that the SQL inbuilt backup utility is not being used.

4. Only a full backup of the master,model and MSDB database may be performed. A Differential or incremental backup of master database is not supported.

Resolution

1. Make sure no SQL backups run outside Backup Exec (including SQL in-built utility for Backups and Restores) as that would interrupt the continuity of SQL backups within Backup Exec schedule causing the job to fail.

Run the following query against the MSDB database for the SQL instance.

select name, backup_start_date
from msdb..backupset
where database_name = 'dbname'
order by backup_start_date desc

where the " dbname" needs to be replaced with the actual database name for which the job fails with the above error.

Note:

Above query can be executed by one of the following methods:

1. SQL Command Prompt which is installed as a part of SQL.

2. Windows Command Prompt by using osql commands.

For example, OSQL -E -S Servername\InstanceName

3. SQL Management Studio Tools which can be downloaded from Microsoft site.

If in the output, the top most entry returns with anything other than "Backup Exec SQL Agent", for instance "NULL", then some other backup application ran the last Full backup.

Backup Exec will in this case cause the Differential/Log backups to fail. (Figure 1)

Figure1:

2. In some cases the differential or log backups may fail with the above error even though no backups have been run outside Backup Exec. This issue is likely to occur if the problem database(s) was restored or detached/attached in the past.

To verify the same, run the query mentioned in point number 1.

3. If the differential/log backup is still failing even though the top most entry is seen as "Backup Exec SQL Agent", verify that the "Backup method" of the last "FULL" SQL backup run by Backup Exec. If the "Backup method" is set to use " Full Copy - Back up entire database (copy)", then the backup will back up entire database or filegroup without affecting future differential or log backups. Changing the "Backup method" to " Full - Back up databases" will enable the "Differential" SQL backup to complete successfully ( Figure 2)

Figure2:

Issue/Introduction

SQL database backup fails with any of the following errors:

Welcome to "KB Articles"