Scheduling Backups for vCenter on SQL Server Express 2005
If you’re running your vCenter on SQL Server Express 2005, you may have noticed that you cannot set up scheduled backup jobs with SQL Maintenance Plans, a feature available in the full version of SQL Server. However, this doesn’t mean you can’t set up scheduled backups at all. In this article, we’ll show you how to set up scheduled backups for your vCenter database using SQL Server Express 2005.
Step 1: Creating a Backup Script
First, you need to create a backup script that you can use to back up your vCenter database. To do this, open the SQL Server Management Studio Express and connect to your vCenter database. Expand the Databases folder, right-click on VIM_VCDB, and select Tasks > Back Up… This will open the Backup Database window, where you can set your backup options.
Set your options as needed, such as the backup file location and retention policy. Once you have set your options, select the Script drop-down menu on the top of the Backup Database window and select Script Action to New Query Window. This will open a script window where you can see the generated script.
Step 2: Saving the Backup Script
Save the generated script by going to File > Save As… and selecting a location to save the script, such as C:\scripts_. In our case, we’ll save the script as FullBackupVCDB.sql.
Step 3: Scheduling the Backup Script
Now that we have a working backup script, we need to schedule it to run at regular intervals. Since we can’t do this within the SQL Server Management Studio Express application, we’ll use Windows Server 2008 R2’s built-in scheduling tool 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:
“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 ensure the schedule runs as planned.
That’s it! You have now successfully scheduled backups for your vCenter database on SQL Server Express 2005. Make sure you include the location for your vCenter database in your regular backup scheme, and you should be a lot safer than before.
Thanks to Chris Dearden over at J.F.V.I for helping us correct our sqlcmd.exe syntax for the scheduled task! vNinja.net is the digital home of Christian Mohn and Stine Elise Larsen.