If you run your vCen­ter on SQL Server Express 2005, you are miss­ing the abil­ity to set up sched­uled backup jobs with SQL Main­te­nance Plans, a fea­ture avail­able in the full ver­sion of SQL Server.

This might not be a prob­lem if your backup soft­ware has SQL Server agents that you use to backup your vCen­ter data­baser, but in smaller envi­ron­ments or even in your lab, you might not have that kind of backup scheme avail­able to you, so what do you do? Thank­fully there are ways of set­ting up the same kind of sched­uled back­ups in SQL Server Express, with­out being a SQL Server Guru.

Cre­at­ing a Backup Script

If you don’t have SQL Server Man­age­ment Stu­dio Express installed already, down­load and install it now.

Fire it up and log on with a user that has suf­fi­cient per­mis­sions to access the vCen­ter data­base

Find your vCen­ter data­base by expand­ing Data­bases and select VIM_VCDB

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

This opens the Back Up Data­base win­dow, where you set your backup options. Set your options in a man­ner that fits your envi­ron­ment. You can set options like the backup file loca­tion, reten­tion pol­icy etc.

So far, this is all fine and dandy. You can cre­ate a man­ual backup this way, with­out much has­sle. How can we turn that into a sched­uled job?
The first bit is to turn your backup options into a SQL script that can be sched­uled. You do this by find­ing the Script drop-down menu on the top of the Backup Data­base win­dow. Select Script Action to New Query Win­dow.

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

The next step is to save the gen­er­ated script, you do so my going to File and select Save … As. I’ve cre­ated 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.

Sched­ul­ing the Backup Script

Now that we have a work­ing backup script, we need to be able to sched­ule it to run. As we can’t do that within the SQL Server Man­age­ment Stu­dio Express appli­ca­tion, we need to find another way of sched­ul­ing it. Win­dows Server 2008 R2 (and other ver­sions) include a sched­ul­ing tool, and that’s what we’ll use to cre­ate our schedule.

On my stan­dard vCen­ter instal­la­tion, the SQL Server is installed in the default loca­tion of C:\Program Files (x86)\Microsoft SQL Server\. This means that the actual com­mand we need to sched­ule will be (be sure to replace the server-/instance-name and script name if your val­ues dif­fer 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 Con­trol Panel and select Sched­ule Tasks. Click Cre­ate Basic Task, give it a name and set an appro­pri­ate sched­ule.

Select Start a pro­gram 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 Prop­er­ties dia­log for this task when I click Fin­ish then click Fin­ish. In the VCDB Backup prop­er­ties, make sure the Run whether user is logged on or not option is selected, to make sure the sched­ule runs as planned.

Once you have ver­i­fied that the sched­ule works as intended, make sure that you include the loca­tion for your vCen­ter data­base in your reg­u­lar backup scheme, and you should we a lot safer than you were.

That’s it! Sched­uled vCen­ter back­ups on SQL Server Express 2005.

Thanks to Chris Dear­den over at J.F.V.I who helped me out with get­ting my sqlcmd.exe syn­tax cor­rected for the sched­uled task!

Written by . Christian is the owner of vNinja.net and a Senior Consultant for EDB ErgoGroup specializing in virtualization. Active twitter user and vSoup.net Virtualization Podcast co-host.