Comprehensive data protection for all workloads
Post Reply
DrRandy
Novice
Posts: 4
Liked: never
Joined: Mar 16, 2011 5:54 pm
Full Name: Randy Stone
Contact:

Advice for SQL DIFF backup failure?

Post by 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
Chief Product Officer
Posts: 31456
Liked: 6647 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Advice for SQL DIFF backup failure?

Post by 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
Novice
Posts: 4
Liked: never
Joined: Mar 16, 2011 5:54 pm
Full Name: Randy Stone
Contact:

Re: Advice for SQL DIFF backup failure?

Post by DrRandy »

I will request that this be tried. I will report back if it works. Thanks for the help.
DrRandy
Novice
Posts: 4
Liked: never
Joined: Mar 16, 2011 5:54 pm
Full Name: Randy Stone
Contact:

Re: Advice for SQL DIFF backup failure?

Post by 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
Chief Product Officer
Posts: 31456
Liked: 6647 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Advice for SQL DIFF backup failure?

Post by 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
Lurker
Posts: 1
Liked: never
Joined: Jun 07, 2011 4:01 pm
Full Name: Brian Craig
Contact:

Re: Advice for SQL DIFF backup failure?

Post by 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
Novice
Posts: 4
Liked: never
Joined: Mar 16, 2011 5:54 pm
Full Name: Randy Stone
Contact:

Re: Advice for SQL DIFF backup failure?

Post by DrRandy »

Support verified the issue and said the fix would be in the next release.
mschlott
Enthusiast
Posts: 84
Liked: 2 times
Joined: Jan 20, 2011 5:45 pm
Full Name: Mike Schlottman
Contact:

Re: Advice for SQL DIFF backup failure?

Post by 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
Service Provider
Posts: 10
Liked: 3 times
Joined: Dec 06, 2010 1:23 pm
Contact:

Re: Advice for SQL DIFF backup failure?

Post by lothargr »

Hello,

Does anyone know if this issue is resolved in version 6?
Gostev
Chief Product Officer
Posts: 31456
Liked: 6647 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Advice for SQL DIFF backup failure?

Post by 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!
Post Reply

Who is online

Users browsing this forum: Semrush [Bot] and 164 guests