SQL Server VM (vadp) - FULL backup fine, Incremental backup a disaster!

Hi GBY,

STEP 1: Had you enabled CBT using command line at proxy host? If not please use the following format:

directory>nsrvadp_modify_vm.exe -H vCenter server -P protocol -u user -p password -l lookup method -k lookup key -c command.

Hi 1goyalp,

I've just tried that command with -c info and it says CBT=Enabled so I don't think the problem is there. I'm going to try stopping the SQL VSS service on the client machine tonight and see if that makes any difference but would be keen to hear any other suggestions.

Thanks

Hi GBY,

The CBT is enabled and it is taking incremental backup. As per the working architecture of CBT it should make the snapshot for each virtual disk. It looks like:

vmname.vmdk
vmname-flat.vmdk
vmname-ctk.vmdk
vmname-000001.vmdk
vmname-000001-delta.vmdk
vmname-000001-ctk.vmdk

In some cases, CBT might reset and lose track of incremental changes and snaphot disk do not get deleted automatically so

STEP 1: Delete all the old snapshots. (Don't try the backup now).

STEP 2: For each virtual disk, this entry must be there scsix:x.ctkEnabled = "TRUE".

Now you have to enable CBT in SQL. The command is something like this:

STEP 3: SQL> alter database enable block change tracking;

You enable block change tracking by making an entry in the controlfile:

(Please take help from SQL DBA to enable CBT in SQL DB, I have bit less knowledge in SQL).

CBT in SQL checks 64KB of every block and it will track all the changed block from DB before backing up and will not pause the running DB to compare and cache the changed block. This will avoid SQL VSS to invoke interrupt handler.

STEP 4: Now try the incremental backup.

Could you please give me the application log event in .evt format and version of vSphere, VDDK, Networker build version of server and client?

1goyalp I think you may be onto something as far as as the VMDK files go.

This VM lives in 2 different datastores, the first one has:

vmname-ctk.vmdk

vmname_2-ctk.vmdk

vmname_2-000001-delta.vmdk

vmname.vmdk

vmname_2.vmdk

the second has:

vmname.vmdk

vmname-ctk.vmdk

vmname-000004.vmdk

vmname-000012.vmdk

vmname-000009.vmdk

vmname-000006.vmdk

I'm hesitant to delete the CTK, DELTA and 00000x files. Is there a command I can run from the ESX host to see which files are actually in use right now?

Also you say this:

STEP 2: For each virtual disk, this entry must be there scsix:x.ctkEnabled = "TRUE"

Whereabouts should I see/set this?

Thanks.

Hi

how often do you run the incremental backups ?

have you tried running a level 1 (differential) backup

At a guess, the backup is initiating a VSS backup of SQL using the SQL VSS writer. Why this fails for incremental backups but works for fulls I'm not sure. Possibly an incremental backup is interpreted as a transaction log backup. Your error messages indicate problems with the "master" and "msdb" databases, the system portion of SQL. These databases cannot be quiesed.

Try looking into the database recovery model for your databases, full or simple. Also, the VMware KB 1031200 might be some help, on how to disable specific VSS writers in VMware tools.

Since you're not using the SQL module, perhaps a file system level consistent image backup is enough for your needs.

Maybe it's just me, but shouldn't you use NMM for the SQL backups?

AFAIK VADP doesn't do applications, so I don't think you'll be able to do a recovery.

Just my 2 cents,

/tony