1

The VMs I run SQL Server on are regularly backed up by the VMware backup tool. I would prefer not to use this backup solution but I have no choice but to cope with it.

The problem I've come up across is the fact that every time the VM backup us ran, SQL Server thinks it has performed a full backup of the databases to a virtual device (physical_device_name = GUID).

I can see those backups in msdb (they are copy_only backups so I don't have to worry).

Do you know why it works this way? Is VMware aware of SQL Server existence? Or maybe SQL Server has wrong data in msdb?

Can I run into any problems with that solution (other than trying to restore the DB from the virtual device)?

4

I would prefer not to use this backup solution but I have no choice but to cope with it.

You are thinking absolutely correct. Please note that VMware backups take backup using snapshot mechanism its not like backup you take using TSQL Command. Its not a good idea to rely on VM backups I strongly suggest you to start takign TSQL backup specially if database recovery model is full and you want to take transaction log backup to truncate logs

SQL Server thinks it has performed a full backup of the databases to a virtual device (physical_device_name = GUID).

This is how SQl Server thinks with any other third party tool. If you use any othet third party tool to take backup the VIRTUAL_DEVICE would be the location where you took backup and this would be logged into errorlog.

Do you know why it works this way? Is VMware aware of SQL Server existence? Or maybe SQL Server has wrong data in msdb?

To explain how it works you must read Information Shedding Light on VDI VSS backups

Can I run into any problems with that solution (other than trying to restore the DB from the virtual device)?

These are just snapshot backups which are not useful when you want to do point int time recovery.

  • Thx for your response. I have implemented regular TSQL backups (FULL+DIFF+LOG) and the automatic restore failed because it couldnt open the newest backup file (which happened to be a virtual one). – BuahahaXD Jun 15 '15 at 7:47
  • I have one question: how do you prepare for restoring of the full machine (say the Windows is corrupted and even though you have DB backups you can't set your SQL Server online because Windows isnt working)? – BuahahaXD Jun 15 '15 at 7:48
  • Yes in that case you can use VMshanpshot and that would be helpful but that is good when you are taking snapshot of whole windows box. But in our case we are focussed on SQL Server and IMHO nothing can be better than TSQL backups – Shanky Jun 15 '15 at 8:48
  • If you are making backups of the SQL server disable the SQL Server VSS writer service. Then the VMWare shapshots will not include backups of the SQL Server databases. – Spörri Jun 15 '15 at 11:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.