Scheduling vCenter Backups

If you run your vCenter on SQL Server Express 2005, you are missing the ability to set up scheduled backup jobs with SQL Maintenance Plans, a feature available in the full version of SQL Server.

This might not be a problem if your backup software has SQL Server agents that you use to backup your vCenter databaser, but in smaller environments or even in your lab, you might not have that kind of backup scheme available to you, so what do you do? Thankfully there are ways of setting up the same kind of scheduled backups in SQL Server Express, without being a SQL Server Guru.

Creating a Backup Script

If you don’t have SQL Server Management Studio Express installed already, download and install it now.
Fire it up and log on with a user that has sufficient permissions to access the vCenter database

Find your vCenter database by expanding Databases and select VIM_VCDB

Right click on VIM_VCDB and select Tasks and then Back Up…

This opens the Back Up Database window, where you set your backup options. Set your options in a manner that fits your environment. You can set options like the backup file location, retention policy etc.

So far, this is all fine and dandy. You can create a manual backup this way, without much hassle. How can we turn that into a scheduled job?
The first bit is to turn your backup options into a SQL script that can be scheduled. You do this by finding the Script drop-down menu on the top of the Backup Database window. Select Script Action to New Query Window.

This opens the script window, where you can see the script and test it to make sure it works as intended.

The next step is to save the generated script, you do so my going to File and select Save … As. I’ve created a folder called c:\scripts\ that I use to store my SQL scripts in, so I’ll save the backup script there as FullBackupVCDB.sql.

Scheduling the Backup Script

Now that we have a working backup script, we need to be able to schedule it to run. As we can’t do that within the SQL Server Management Studio Express application, we need to find another way of scheduling it. Windows Server 2008 R2 (and other versions) include a scheduling tool, and that’s what we’ll use to create our schedule.

On my standard vCenter installation, the SQL Server is installed in the default location of C:\Program Files (x86)\Microsoft SQL Server\. This means that the actual command we need to schedule will be (be sure to replace the server-/instance-name and script name if your values differ from mine):

“C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE” -S [servername]\SQLEXP_VIM -i c:\scripts\FullBackupVCDB.sql

Go to the Control Panel and select Schedule Tasks. Click Create Basic Task, give it a name and set an appropriate schedule.

Select Start a program as the action for the task, and when it asks for Program/Script enter “C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE” -S [servername]\SQLEXP_VIM -i c:\scripts\FullBackupVCDB.sql.

Click next and check the box that says Open the Properties dialog for this task when I click Finish then click Finish. In the VCDB Backup properties, make sure the Run whether user is logged on or not option is selected, to make sure the schedule runs as planned.

Once you have verified that the schedule works as intended, make sure that you include the location for your vCenter database in your regular backup scheme, and you should we a lot safer than you were.

That’s it! Scheduled vCenter backups on SQL Server Express 2005.

Thanks to Chris Dearden over at J.F.V.I who helped me out with getting my sqlcmd.exe syntax corrected for the scheduled task!

15 Comments

  1. Hi everyone,

    Nice post. I’m reading a little (too much) about vSphere/ESX lately. (self learning purposes).
    I came to this blog/post after reading Gabe’s Virtual Globe Blog about DC virtualization…

    Anyway, I just want to point you a really “must have” maintenance solution to just any version of SQL Server (Express included).
    A Sir called Ola Hallengren had developed the most powerfull maintenance solution for MS SQL Server. The best of all, it is free (donations accepted though). In case of interest, please visit http://ola.hallengren.com/ and try it yourself.

    Regards,
    Luís

    1. @Kayser: You restore it like you would any other old SQL database. Get a working instance going, and restore from file. There are loads of information on that available from Microsoft.

  2. Great script and a great intro to some basics of SQL Server Management Studio Express – for those of us for whom this is unfamiliar.

    One tip, though, maybe I did something wrong — but I found that the backup file grows and grows. I have a network monitoring application that keeps an eye on disk space on servers, and it caught this one dwindling down to under 15% free. I found that the backup file was almost 7GB, but the source database is only around 800MB.

    I had assumed that the backup would just get overwritten every day, so I set up VSS on this disk and was relying on Shadow Copy if I needed to go back a day or two (and VM backups if I needed to go back farther).

    Now I need to set up another housekeeping script that will rename the file every day and clean up old copies. Or, just get in the habit of doing it manually every now and then (purge the .bak file and re-run the scheduled task manually to recreate a small backup).

    Thanks! Happy New Year!

    1. @Doug: You are right, there are better ways of doing that, in the SQLCMD.EXE command, you can add something like %DATE% to get a new name every day. Something like this:

      C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE” –S [servername]\SQLEXP_VIM –i c:\scripts\FullBackupVCDB-%DATE%.sql

  3. Great howto, and as Doug says. It would be better to write a backup file for every day and not all days in one .bak file.

  4. Hi,
    I am looking my vcenter backup which is with out sql server. So how can i backup my vcenter. Please help me on it.

Leave a Reply