Hyper-V backup conflicts with SQL backup

[Dude16]

I am using Backup Exec to back up 5 of my Hyper-V machines every Thursday morning. One of the virtual machines is a SQL server. On this SQL server I am separately running a full backup of a database on Sunday mornings and differential backups every 4 hours for the rest of the week through SQL Server's built in "SQL server agent". After a catastrofic failure where I had to recover my database, I discovered that the Hyper-V backups are messing up the SQL Server's backup set. I was not aware of this but when Backup Exec does a Hyper-V backup, it takes a full snapshot of my SQL database. Not sure why it does this but I see it in the SQL servers logs. With this action, Backup Exec breaks my backup set, performed by SQL server, because it inserts an unwanted full backup in between my explicit Sunday full backup and any post Thursday differential. This breaks the pairing of my differentials to my Sunday full backup because SQL sees that a newer full backup has been performed in between.

Question:

How do I stop backup exec from doing this. I have not specified a SQL backup in Backup Exec. I only want it to do the Hyper-V VHDs as a whole. Any other suggestions to prevent this conflict?

[pkh]

I don't think this can be done.  The SQL database is backed up as part of the VHD so that you can do a GRT restore of your database using the VHD backup.  Quoting from the document below "If AVVI or Hyper-V backup jobs are still scheduled, these must be scheduled so that they do not interrupt the sequences of SQL Agent Full and Log backup jobs."  Athough this refers to full and log backups, this is equally applicable to full and differential backups.

http://www.symantec.com/docs/TECH137587

[Dude16]

I think this other article which was linked from your link more acurately relates to my problem. 

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

As the "Cause" section states:

"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 my situation the exact opposite occurs. BE is reseting the LSN during the Hyper-V backup rendering the differentials created by SQL's own backup agent unrecoverable.

I don't understand what the solution is though.

[pkh · Accepted solution]

The solution that you are referring to is asking you to check that the last full backup is done by BE. In which case, the error is caused by something else.

In your case, it is clear that the VM backup is interfering with your sequence of backup, just like the case cited in the document I referred to earlier which is for log backup.  The document states that "If AVVI or Hyper-V backup jobs are still scheduled, these must be scheduled so that they do not interrupt the sequences of SQL Agent Full and Log backup jobs."  Since it does not offer any alternative, it means that in situations where the VM backup is interfering with the SQL backup sequence you have to schedule the VM backup in such a way that it does not interfere with the sequence of the SQL backup.  In other words, you cannot stop the full VM backup from interfering with yout SQL backup sequence.  You just have to schedule your way out of this "interference"

[Dude16]

I was afraid of that. It looks like the only solution is to schedule a full SQL agent backup imediately after the hyper-v backup. Any differential backup after that is OK up until another hyper-v backup runns which invalidates all the differentials. Then a full SQL agent backup is needed again after that hyper-v backup. Clumsy solution.

The key to the problem is this as Symantec states in their own bulletin:

"This issue occurs because the Log Sequence Number (LSN) for SQL is reset by the competing backup agent" 

They know why this is happening. All they would need to do is have an option to opt out of the LSN reset. I hope they do this in a patch or next version.