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"
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.
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.
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.
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.
Run the following query against the MSDB database for the SQL instance.
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.