cancel
Showing results for 
Search instead for 
Did you mean: 

SQL VMDK Incremental - Two backups each run?

AVL_IT_GUY
Level 2

Each time our incremental backup job runs, SQL registers two backup jobs. Is this normal behavior? Here are the details:

  • Backup Exec 16
  • SQL Server 2014 running on Windows Server 2012 R2 in a vmware vSphere 6.5 environment
  • Full backup runs nightly at 9:00 PM
  • Incremental backup runs hourly from 6:00 AM to 9:00 PM
  • Verify - At the end of the job
  • Instant GRT - Enabled
  • Advanced Open File - Use snapshot technology, Automatic VSS provider, Process logical volumes one at a time, enable checkpoint restart
  • Virtual machine options
    • Use the full backup method...
    • Use GRT 
      • Enable GRT for MSSQL
        • Run a SQL Log backup after backing up the VM
    • VMware
      • Use VSS
        • Use VSS Copy backup
      • Transport mode - NBD
    • Full backup method: Full - back up virtual machines
    • Incremental backup method: Incremental (block-level)

When reviewing the BackupExec log for the job, it runs the VMDK backup first and includes that it "discovered and protected Microsoft SQL Server data on virtual machine 'server.mydomain.com'". That takes about 4 minutes. 

It then proceeds to the SQL-specific backup. It performs consistency checks and then backs up the 13 databases. 

I think that's all normal and to be expected. The DBA managing the application in question sees the following behavior in the SQL logs during an incremental backup (attached):

  1. I/O is frozen for each DB for 1 second
  2. I/O is resumed for each DB
  3. Each database is backed up (according to the log, but we're only 2 seconds in now)
  4. I/O is frozen for each DB for about 5 seconds
  5. I/O is resumed for each DB
  6. Each database is backed up.
  7. There's now a 4 minute gap. I'm guessing this is during the verify stage of the VMDK backup.
  8. Each database receives consistency check
  9. Log backup performed for each database

My DBA is only expecting to see steps 8 and 9.

The most important question for me right now is this: Am I correct in my understanding that the VMDK backup is what is logging steps 1-6 (i.e. this is expected behavior)?

Secondarily, is there a more efficient way to be doing this or is this the best way to protect both the VMDK and SQL?

1 REPLY 1

Riyaj_S
Moderator
Moderator
Employee Accredited Certified

Since option "Run a SQL Log backup after backing up the virtual machine" is selected in VM job (VMware agent), you are seeing events 1-6. When this option is selected, Backup Exec backs up the SQL logs for the databases that use logging. After the logs are backed up, the data from the logs is committed to the database and the log is emptied so that it can receive new data.

More details are documented in : https://www.veritas.com/support/en_US/article.000109164

Since you are already running a separate backup job to protect SQL database and logs it is not required to select the above option in VMware job.

In case of disaster recovery scenario you may recover the complete VM using VMware backup set and run another restore job to restore SQL databases and logs.

Thanks and Regards,

Riyaj