Advice for SQL DIFF backup failure?

[DrRandy]

Hi,

I'm a DBA. We support a few SQL Servers in ESX. Most of our SQL Servers are physical. We have SQL Server jobs that do full, diff, and log database backups. Recently the SQL Server job that does the diff database backups started failing in the VMs. The Server team is using VEEAM to do backups. It appears that SQL Server is aware of the VEEAM system backup. Unfortunately, SQL Server detects the out-of-band backup and refuses to perform a diff backup until a new full database backup is done. Do we need to do the full database backup after the VEEAM backup to support a restore to a point in time? Do we have to do a full database restore after a VEEAM restore to apply the SQL diff and transaction log backups? Do we need a separate backup for the SQL backup files?

The server team does the backup once a night. Their interest is to have a fully consistent system backup. It is nice that they can restore to any full system backup the prior 30 days. However, DBA wants to be able to restore any one database to a point in time to the original server or to another server. Is there a COPY_ONLY option for a VEEAM backup so that it does not affect the SQL Server backup jobs?

I only know a little about VEEAM and VSS. Any advice would be appreciated.

Thanks,
Randy

[Gostev]

Hi Randy, try to disable transaction logs processing for this SQL Server VM (click Advanced on Guest Processing step of backup job wizard). Thanks.

[DrRandy]

I will request that this be tried. I will report back if it works. Thanks for the help.

[DrRandy]

Hi Anton. The option "Do not truncate logs" was already set. (Was this the option you meant us to set?) The option "Enable application-aware image processing" is checked. I think the issue has more to do with how SQL Server is handling the full backup, not the transaction logs. If it's not COPY_ONLY, then it will affect the SQL Server full backup sequences stored in SQL Server. Can we safely do without application-aware image processing on a SQL Server? Is that crash consistent? The server team is contacting Veeam Support for advice.

There is some support in SQL Writer for COPY_ONLY (http://msdn.microsoft.com/en-us/library/cc966520.aspx). It would be a nice option for SQL Server "out-of-band" backups. If this was an option, we could choose to isolate our normal SQL-Server backups from the system level backups.

[Gostev]

Hi Randy, thanks for the link, we will investigate this.

Yes, without "Enable application-aware image processing" the backup will be crash-consistent. It is safe for production SQL as long as you do not try to enable VMware Tools quiescence in the advanced job settings (this is disabled by default). I think crash-consistent backups is a way to go in your case, since you are backing up all SQL data separately (and in consistent manner) anyway.

Thanks.

[brianacraig]

Gostev,

I'm having the same problem with my differential backups. I was wondering if any progress had been made on this issue, or if I need to disable the "Enable application-aware image processing" option in order to get differential backups to work.

Thanks,
Brian

[DrRandy]

Support verified the issue and said the fix would be in the next release.

[mschlott]

I had this same problem. When I started backing up our SQL server in our dev environment, the DBA began reporting that his backups were failing as you described. I worked with Veeam support and it was reccomended that I turn off a "Enable Application Aware Image Processing", which uses VSS as did our SQL backups. Once I disabled that, our SQL backups began working again.

Hope this helps.

mike

[lothargr]

Hello,

Does anyone know if this issue is resolved in version 6?

[Gostev]

Kind of. You can make it working now. Just disable Veeam app-aware processing, and enable VMware quiescence in the advanced job settings. In this case, VMware Tools VSS integration will send COPY_ONLY to the application. Thanks!