VM backup of SQL server breakes Log backup sequence

[YonatanC]

Hi,

Here's my scenario:

Backup exec 2010 R3 SP3.

Everyday (inc. weekends) at 05:00 AM we're having a full backup of the databases of 6 SQL servers, and after that we do a log backup every 5 hours (one job with a selection list of all the DBs on all SQL server).

All SQL servers are on Windows 2003 except one which is on Windows 2008 (computer name: SQL9).

Every Friday at 15:00 I have a job that takes a VM backup of all those SQL. The job is defined to use AOFO with Microsoft Shadow Copy.

The Log backup on 13:00 is successful but the Log backup on 17:00 always fails to backup the DBs on SQL9, saying:

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

I tried to exclude SQL9 from this job and created a seperate job for him without the AOFO, but still same problem.

On the SQL server logs I see that the VM backup realy backed up all the DBs.

I don't understand why is happaning only with one SQL server and not with the rest? What is the difference between them?

Thank you 

[pkh]

I don't have the document at the moment, but you are not suppose to do VM backups between your full SQL backups and the log backups. As you have discovered, this will break the sequence. For your SQL backups, you should not turn on AOF. Otherwise you may have problems when you do a redirected restore of your databases

[YonatanC]

So how come the VM backup doesn't interfere all the other SQL servers we have?

One important thing I forgot to mention - all the databases on all the SQL are sitting on RDM disks, so the VM backup doesn't even back them up (it gives an error in the job log about not being able to backup RDM disks).

I even compared to the log of another SQL server and I see the DBs are not backed up during the VM backup. Only on server SQL9 the DBs are being backed up, although they sit on RDM disks.

The VM backup is basicaly intended for a quick full restore of the machine in case of need, not for the restoration of specific database.

[YonatanC]

One more thing - you said I'm not supposed to run VM backups between the full and log backups. But I'm running it between two log backups...

So what you're saying is I should run it after the last log backup and before the new full backup?

[SnirA]

Hi,

When you do a vm backup using quiesced method, it will query all the vss services that you see in:

vssadmin list writers and will tell them to do a vss backup.

since its an sql server, sql has its own vss writer. saying that, a quiesced backup will turn to the sql vss writer and tell it do a backup. this is how you get an inconsistency.

the sql "thinks" you did a backup and mark the DB as being backed up after the sql vss writer did its thing.

once you go to netbackup and ask for log backup or diff backup, you see that the chain is broken.

- for log backup u see it while you are doing the backup.

- for diff backup you only see it when you tries to do a restore of full + diff and there is a VM backup between them.

links that explain what i said:

https://www-secure.symantec.com/connect/blogs/concept-snapshot-levels-and-quiescing

link that says how to solve it:

https://www-secure.symantec.com/connect/blogs/concept-snapshot-levels-and-quiescing

one more thing,

you mentioned RDM earlier, that the sql is built on.

you should know that it dosent matter, the quiesced backup will activate the "sql vss writer" and it dosent matter where the sql at, it will think you did a backup.

this is typical and intended behaviour for VMware quiesced backup.

[pkh]

Yes. You should run your VM backup just before the fill backup

[YonatanC]

Hi Snir,

Thanks for the detailed explanation! I will sit and read te first link today to better understand everything.

Only thing is that the second link you posted, with the solution, is identical to the first one...

And one more thing, how come this doesn't happens with all the other SQL servers? Is it something to do with Windows 2008?

[SnirA]

Hi,

Here is the missing link:  http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=103120...

this depends if the sql vss writer works correctly, you can manualy run quiesced snapshots from the VC on the specified machine and look at the eventvwr for information about sql backup.

I didnt check for 2003 but you can see for each machine for itself.

its clear if you look at the eventvwr once the snapshot is being taken, you will see sql information or error events.

[SnirA]

Hi, seems like i cant post another sites links here so:

look for this at google:

Disabling specific VSS writers with VMware Tools (1031200)

- its a vmware KB explaing how to disable specific vss writers.

a smiple way to check if this issue happens on your machine is to look for sql backup events in your event viewer once you take the quiesced snapshot.

you can see either backup was good or it will print out errors for fail backups.

[YonatanC]

Well, I do see sql backups in the event viewer on the sql server, and also on the SQL Management logs.

But I still don't understand why is this happening only with one sql server and not with the rest of them?

Also, I don't think I can just disable the SQL VSS Writer for good. I'm sure it's used by other services, not only by Backup Exec...

[SnirA]

It can be becuase of sql writer issues or becuase of version compliance.

disableing the sql writer will only affect backups that are using the VM and nothing else.

there is another option for you.

you can devide the backup into 3 parts.

1. backup sql like you do now.

2. backup boot disk with VM - without quiesce.

3. backup rest of the server excludeing things you do need.

this way you get:

1. valid sql backup.

2. a fast restore for machine(though not 100% recoverable)

3. normal backup that will cover you with everything else not backed up.

[YonatanC]

OK, Just want to be sure -

If I'll disable the sql vss writer, it won't affect other backups that are done using the agent? It will only affect VM backups done by Backup Exec?

Also, I will be able to quickly restore the entire machine from the VM backup and than restore the SQL from the SQL backup I have?

And one more thing - Is it possible to just disable the service called "SQL Server VSS Writer"?

[SnirA · Accepted solution]

Hi,

answer is yes for all the questions, you will be able to do all.

- please do a test to see all is working for you after the changes.

*edit: it will only effect snapshot backup that uses quiesce wether its from BE or VC itself dosent matter.

Good luck!