VCB + Windows 2000 + SQL 2000

[Fpwr]

Hi Guys,

I'm trying to know if I take a backup with VCB of one of my VM (Windows 2000 + SQL2000) in full vm mode the SQL database will be consistent or not ... I know that since ESX 3.5 update 2 VSS support has been improved and should not cause not consistent DB backup ... But as my OS and SQL version are not VSS aware I would like to know the result of a backup on the DB !!! In addition what about a SQL 2000 on Windows 2003 ?

Thanks for your answers

Regards

Julien

[apatel1]

Hi Julien, the database will only be crash consistent in your backup regardless of what version of ESX/VMware Tools you have.

Pre-Update 2 versions of ESX/VMware Tools used the SYNC driver to quiesce the file system inside the VM. However, the SYNC driver does nothing for databases or other applications. Update 2 and onward, provided you are running the latest VMware Tools and have installed the VSS option, you will have VSS support for application-consistent and file system-consistent backups on Windows Server 2003 guests containing VSS-capable applications. Nothing changes for Windows 2000 versions, though.

That being said, you could still use a "pre-freeze" script with a SQL query to perform a SQL-based backup (BACKUP DATABASE blablablablabla...) to a file before VCB takes the snapshot. That way, the snapshot will contain a copy of the SQL .bak (and .trn if you choose to backup transaction logs) files. That way, your backups will contain consistent copies of your databases, though restoring would require the additional step of restoring the database inside SQL after restoring the VM itself.

Please help me out by awarding points for a "helpful" or "correct" response if you feel that it was useful!

-Amit

[ceemour · Accepted solution]

Windows 2000 is not vss aware.

SQL2000 is vss aware.

Move to Windows 2003 with sql 2000 and you will be good.

Regards