Differential MS SQL Backup's Keep Failing

[Thundercleese]

I recently setup a differential backup job for a MS SQL (SQL 2008 standard) database, but I'm having trouble getting it working.  The diff. job works only once; after a full DB backup.   Every other time it runs I get the following error:

Error Code: E0000363
The job failed with the following error: The Backup Exec SQL Agent was not used to create the last full, differential, or log backup for this database.  You must use the SQL Agent to run a full backup before you can run a differential backup or transaction log backup.

For additional information regarding this error refer to link V-79-57344-867

Using the SQL Management Studio Tools I ran the suggested SQL query to see if any other backup jobs were running.  The result for every database I picked was the same: Backup Exec SQL Server Agent.  Not sure what else I can try to resolve this problem.  Feedback much appreciated.

Below is how I setup my differential Microsoft SQL DB backup jobs.

Weekly Full

• Backup Method: Full - Back up entire database or filegroup
• Consistency check before backup: Full check, include indexes.
• Consistency check after backup: Full check, include indexes.
• Use Checksum on backups (SQL 2005 or later)

Daily Differential

• Backup Method: Differential - Back up database or file group changes only.
• Consistency check before backup: Full check, include indexes.
• Consistency check after backup: Full check, include indexes.
• Use Checksum on backups (SQL 2005 or later)

Daily Log Backup (after differential)

• Backup Method: Log - Back up transaction log.
• Consistency check before backup: Full check, include indexes.
• Consistency check after backup: Full check, include indexes.
• Use Checksum on backups (SQL 2005 or later)

[Donald_Eady]

Thundercleese,

      Give these two documents a look over.

http://www.symantec.com/business/support/index?page=content&id=TECH58674

http://www.symantec.com/business/support/index?page=content&id=TECH137587

[Jaydeep_S]

The Incremental backups running after Differential are causing the failure. Choose either of the backup methods.

[Thundercleese]

I already tried the solution in the first link, but unfortunately the only result I get from the query is: Backup Exec SQL Server Agent.  I doubled check, and my full DB backup job is set to Back up database or file group changes only.

The second link doesn't apply to my environment.  The MS SQL DB is running on a physical server box (no virtualization).

Thanks for the documents.

[Thundercleese]

I don't have any incremental backup jobs for the DB.  More correctly I've disabled them all while attempting to get this differential backup solution to work.

[Donald_Eady]

Do you have just the one SQL backup job scheduled for that server. "Full". Having more than one full SQL backup even if created by B.E. may cause an issue.

For example

SQL full - a   Successful

SQL diff - a   successful

SQL full - b   successful

SQL diff -  b   successful

SQL diff -     a fail due SQL full b running between this job and last full for SQL A

would you be able to provide the results of the query ... Blocking any sensitive information "server names"

[lmosla]

Hi Thundercleese,  is there any scheduled SQL database maintenance running?

Lenora

[Thundercleese]

Yes, I have one backup job for the entire database (total of 186GB).

Example of the MS SQL DB job's run:

MS SQL FULL - Successful
MS SQL DIFF - Successful
MS SQL LOG  - Successful

Next day:
MS SQL DIFF - Failed; error E0000363
MS SQL LOG  - Successful
....

[Donald_Eady]

Ok Thunder,

               I think that i know whats going on... i believe that the incremental/log backup between the first diff and the second are whats is causing the issue. The log back up is flushing the transaction logs so when the diff attempts to run it does not see what it is expecting. would it be possible to schedule your log backups to preceed the full.

possible

MS SQL FULL

MS SQL DIF

next day------

MS SQL LOG

MS SQL FULL

MS SQL DIFF

next day ---

MS SQL LOG

MS SQL FULL

MS SQL DIFF

[Thundercleese]

Interesting, I never would have thought that the log back up might be flushing the transaction logs.  I could setup a backup job like you suggested, but if I'm doing daily MS SQL FULL there wouldn't be a reason for the differential, right?  Probably bad practice, but I wonder if things would work if I ran a LOG backup before the weekly FULL?  Something like:

weekend
MS SQL LOG
MS SQL FULL

weekday
MS SQL DIFF
MS SQL DIFF
MS SQL DIFF
MS SQL DIFF
MS SQL DIFF

...

[Thundercleese]

No, there does not appear to be any scheduled SQL database maintenance running.

How I checked, using Microsoft SQL Server Management Studio:

• Expanded the Management folder.
• Right-click on Maintenance Plans and selected View History (Log File Viewer windows appeared).
• Selected option to view Maintenance Plans logs; no logs appeared.

[pkh]

Backing up the transaction logs and truncating them will not affect the sequence.  You should not truncate your log before a backup.  If you do and your database backup fails, you would not be able to do a point-in-time restore beyond the last database backup.  Always truncate your logs AFTER a backup, not before.

If you do not have any other database maintenance going on and the LSN is correct, I would suggest that you start a support case with Symantec for them to take a look at the problem.

[Donald_Eady]

Your suggested backup schedule should work fine. Please update us after a few backups run

[Thundercleese]

The differential backup job will run fine for a few days after a full DB backup, but then it will start to 'error out (E0000363)'.  Very odd...

[lmosla]

Hi,

Have you reviewed the Windows events on the SQL server and the Media Server for errors that maybe occurring during the time of the backup job? This might identify if any other applications are getting in the way of the backup.

Also, do you have any Antivirus programs running?  Here is a link for a How To on making exceptions.  It refers to thoroughput and SEP but can give a general over view of other programs/problems too. http://www.symantec.com/business/support/index?page=content&id=TECH168940