Veeam Backup & Replication and SQL 2000

[klasikrkade]

Has anyone come up with any type of graceful or not so graceful solution for backing up a vm that consists of server 2000 and SQL 2000 while quiescing the database as to not lose data? Not having VSS as an option and having no way, seemingly, from within Veeam Backup and Replication to run any sort of pre job scripting to, for instance, pause SQL etc I am at a loss as to how I am going to backup these vm's and have any sort of database corruption protection for the SQL 2000 databases. Veeam seems to have the option for running post job commands but for some reason no pre job commands.

Any suggestions?

Thanks

[Gostev]

Our customers use VMware pre-freeze and post-thaw scripts to address scenarios like this (stop and start services during hot backup). This works perfectly, which is why we do not really need pre-job scripting.

Typically most pre/post scripts are used by our customers backup applications on Linux VM, for example (no VSS there either). Also I've seen report of people doing this for Oracle running on Windows. So I don't see why this would not work for SQL 2000 on Windows 2000. There are a number of existing topics about using pre/post script on both Windows and Linux, you can look them up for more information.

[tsightler]

I'd also point out that snapshots of such environments are generally at least crash consistent. Assuming you use the Full recovery model the likelyhood of a database not being recoverable from a snapshot is very, very low, basically the same as if you just hit the power button (actually slightly better since might leave cached writes unwitten to disk where a snapshot won't cause this). Just make sure you don't use the VMware Tools quiesce option as that actually seems to make things worse.

Also, one regularly overlooked option, if you use the "Full" recovery model, you can always restore the previous nights backup, and the use FLR to restore the transactions logs and play those logs forward to recover to a point-in-time. Since in this case you would always have any required to recover the database you would be safe.

I have been using snapshot backups for 10+ years, long before Veeam and VMware snapshots, and, assuming you plan carefully so that logs are always snapped last, or simultaneously, or are copied using an alternate method, you can always recover from snapshots. Well, maybe I shouldn't say "always" since things can also "always" go wrong, but I'd take my chances with a snapshot backup of some backup dumped to tape any day.

[klasikrkade]

I found this:

@echo off
if "%1" == "freeze" goto freeze
if "%1" == "thaw" goto thaw
if "%1" == "freezeFail" goto freezeFail

:freeze
net pause "SQL Server (MSSQLSERVER)"
exit
:thaw
net continue "SQL Server (MSSQLSERVER)"
exit
:freezeFail
net start "SQL Server (MSSQLSERVER)"
exit

It does not seem to work. When I run my backup i get the following:

Creating snapshot
CreateSnapshot failed, vmRef "vm-2206", timeout "1800000", snName "VEEAM BACKUP TEMPORARY SNAPSHOT", snDescription "Please do not delete this snapshot. It is being used by Veeam Backup.", memory "False", quiesce "True"
Cannot create a quiesced snapshot because the (user-supplied) custom prefreeze script in the virtual machine exited with a nonzero return code.

How can I edit this batch file so that it works? Any help would be appreciated.

[Frosty]

If its exiting with a non-zero code, then something in your batch file is not working right. You can test for this by checking the ERRORLEVEL value after each command you run in that batch file. e.g.

net pause "SQL Server (MSSQLSERVER)"
if ERRORLEVEL 0 goto PausedOK
if ERRORLEVEL 1 echo Noticed error 1 occur

[scott.hale]

[merged]

I know this is olddddddd news, but do you have any pointers (KB's, etc.) on doing a VM backup of a WIN2K server with SQL2K running on it ?

Obviously there is no WIN VSS...any info or pointers to info would be greatly appreciated.

Thanks -

SH

[tsightler]

The honest truth is, I would probably just setup "normal" full database or log backup schedule with SQL Enterprise Manager to backup to a local folder on the server, and then use Veeam to backup the server. In 99% of cases you would be able to just restore the Veeam image, but, if for some reason the SQL database was indeed damaged, you could just restore the local backup of the SQL database after the Veeam restore was complete.

[Vitaliy S.]

And here is the script that should help: Pre-freeze and post-thaw script example

[scott.hale]

[merged]

I am convinced that there is really no good way to backup a VM Windows 2000 server with SQL 2000 running on it (since there is no VSS). Best option ? Shut down the system, back it up, and then start it up again.
Do you know if there is a way to script shutdown and startup of the target 2K VM from within Veeam B&R ? Thanks.

[jxcclarke]

[merged]

Hi,

After recently performing a succesfull restore of a windows 2000 server the backup is reporting a Waring when completed, it didnt give this warning before the restore and does apear to be backing up the full amount of data, i hvaent trid a restore from it yet though.

Please advise

Regards

Starting guest agent
[VssGAConn] Guest operation system is not supported, type EVeeamOs2000, platform EVeeamX86, version 5.0

Freezing guest operating system
Veeam Guest Agent is not started

[Vitaliy S.]

Application-aware image processing is not supported for Windows 2000 guest, because Windows 2000 does not have VSS framework. You should be using the recommendation above to have application-consistent backups and deselect "application aware image processing" checkbox for this VM.

[jxcclarke]

Thanks Vitaliy

It worked a treat

[Platform1]

[merged]

I called support to ask if Veeam supported backing up SQL2000 on Server 2003 but was referred to the forums.

If I read correctly there's two options, can someone please confirm if this is correct?

1. Perform a SQL backup and run a Veeam backup job with the option ‘application aware processing’/VSS disabled

2. Save the script below as pre-freeze.bat in the folder "C:\Program Files\VMware\VMware Tools\backupScripts.d"
- Run a Veeam backup job with advanced option ‘VMtools quiescence’ enabled

Code: Select all

echo startScript >> c:\a.txt
if "%1%" == "freeze" goto doFreeze
if "%1%" == "freezeFail" goto doFreezeFail
if "%1%" == "thaw" goto doThaw
goto EOF

:doFreeze
echo freeze >> c:\a.txt
net stop "SQL Server (SQLEXPRESS)"
goto EOF

:doFreezeFail
echo freezeFail >> c:\a.txt
net start "SQL Server (SQLEXPRESS)"
goto EOF

:doThaw
echo thaw >> c:\a.txt
net start "SQL Server (SQLEXPRESS)"
goto EOF

:EOF

[Vitaliy S.]

Yes, that is correct. Good understanding of the whole process I should say.

[Platform1]

Thank you. I missed this out, is it recommended to enable 'Vmware tools quiescence' for option 1 as well?

[Gostev]

We highly recommend to avoid enabling VMware Tools quiescence option, as it is known to corrupt active SQL servers due to the use of VMware SYNC driver (which is what is being used to quiesce the file system when VSS is not available). You are better of doing crash-consistent backups, at least this will not affect your production SQL server. Thanks.

[nschweitzer]

I'm trying to backup all my VMs in one job to take maximum advantage of dedupe, however, it looks like you cannot control "VMware Tools Quiescence" on a per-VM basis. Is there a behind-the-scenes method to enable "VMware Tools Quiescence" for a single VM in a backup job?

Gostev mentioned in a post that AAIP overrides the VMware Tools Quiescence option, so could I enable them both for the job and disable AAIP on this particular VM? If so, is this behaviour that will work long-term?

[Gostev]

Interesting idea, this might actually work

[nschweitzer]

Looks like it worked. The backup completed successfully. All the VMs correctly appeared or didn't appear in the VeeamVssSupport log.