Differential/Log backup of SQL database fails with the error - 0xe0000363 The Backup Exec SQL Agent was not used to create the last full backup of this database

Article:TECH58674  |  Created: 2010-01-28  |  Updated: 2014-03-06  |  Article URL http://www.symantec.com/docs/TECH58674
Article Type
Technical Solution


Subject

Issue



Differential/Log backup of SQL database fails with the error "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"


Error



 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 <dbname> was recently restored. A new Full backup is needed before incremental, differential, or transaction log backups can be run.

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

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 BESR 7.0 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 BEWS are relative to the other backup application instead of the original BEWS 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.

In Backup Exec 11D (post Hotfix 38) and later versions, a change has been made to the Backup Exec Agent for Microsoft SQL Server. 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.


Solution



 
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-Only (SQL 2005 or later) - Back up entire database or filegroup", 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 entire database or filegroup" will enable the "Differential" SQL backup to complete successfully (Figure 2)
 
Figure2:
If the differential/log backup is failing in spite of the top most entry being "Backup Exec SQL Agent" and the backup method is set to "Full - Back up entire database or filegroup"  then to resolve the issue please apply the relevant hotfix depending on the Backup Exec version being used. Please refer the Related Documents section below for more information. 

Supplemental Materials

SourceETrack
Value1235427
Description

SQL: Log/Differential backups fail in BE 11 post HF 38 and in BE 12.0


SourceUMI
ValueV-79-57344-867
Description

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.


SourceError Code
Value0xe0000363
Description

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.



Legacy ID



301508


Article URL http://www.symantec.com/docs/TECH58674


Terms of use for this information are found in Legal Notices