OCS 2007 R2 Standard Edition Front End Automated Backups

OCS 2007 R2 Standard Edition Front Ends utilize SQL 2005 Express with SP2 for storing its databases.  Unfortunately, with SQL Express, you will have to backup using SQL Server Management Studio or find an automated way.  This article will detail the steps I utilize to make backing up easier and automated. For information on how to back up OCS, please see the Backup and Restoration Guide here.

The following data will ultimately need to be backed up:

1. Global Config
2. Pool Config
3. Machine Config
4. SQL Databases
5. Standard Edition File Shares

The first command specifies the /level to be global and pool.  The second command specifies the /level to be machine.  What we will do is create a batch file (.bat) and place both commands in this .bat and have them run against the server every 6pm using scheduled tasks.

Our Servername is SHUD-OCSFE01.  The folder to store the backups is C:\OCSBackup.  We’ll also be running the batch file from the C:\OCSBackup.  Because the folder which contains lcscmde.exe is not a part of the system variables, we’ll have to specify the entire path for lcscmd.exe. Taking this information into consideration, our two commands for our batch file will be:

After executing this .bat file, we can see the two files have been created.

SQL Databases

The following is the list of SQL Databases that an OCS Standard Edition Front End uses:

Because we are utilizing SQL Express, we will have to find some other method other than a backup agent to automate the backups. Much of the SQL Backup information is provided by the SQLDBATips Blog.  The following article I utilized is located here.

Create a file with the extension of sql in our OCSBackup folder.  Also, create a new folder called C:\Reports for script reporting. I created a file C:\OCSBackup\ocssqlbackup.sql with the following text:

exec expressmaint
@database      = ‘ALL_USER’,
@optype        = ‘DB’,
@backupfldr    = ‘c:\ocsbackup’,
@reportfldr    = ‘c:\reports’,
@verify        = 1,
@dbretainunit  = ‘days’,
@dbretainval   = 1,
@rptretainunit = ‘weeks’,
@rptretainval  = 1,
@report        = 1

exec expressmaint
@database      = ‘ALL_USER’,
@optype        = ‘LOG’,
@backupfldr    = ‘c:\ocsbackup’,
@reportfldr    = ‘c:\reports’,
@verify        = 0,
@dbretainunit  = ‘days’,
@dbretainval   = 1,
@rptretainunit = ‘days’,
@rptretainval  = 1,
@report        = 1

All of our OCS Databases are User Databases, not System Databases.  We can see this using SQL Server Management Studio which is not installed by default but can be downloaded from here.

Note: Keep in mind that we’re not using the default SQL Express instance of SQLExpress.  The OCS Front End Standard install will create and utilize an instance of RTC.

We now have our .SQL file created.  We’ll go ahead and create a new .bat file called ocssqlbackup.bat.  This batch file will run the following command:

This won’t work just yet.  You can see in the .SQL file, it’s calling the stored procedure “expressmaint.”  We need to create this stored procedure within SQL.  SQLDBATips has the vbscript code in order to do that here.  You take this code and save it as storemaint.sql.  Then run the following code:

Note: The website that shows these instructions specify the -S.\ as -S.\SQLExpress.  Again, we’re not using the SQLExpress instance, but rather the RTC instance.

You can delete the expressmain.sql file now.  This is a permanent change in our instance and we won’t need to run the expressmain.sql script again.

We should now be able to run our SQL backup batch file as our .sql command that specifies our databases and logs has been created and our batch file to call sqlcmd.exe to execute our .sql file has been created.

We can see our ocssqlbackup.bat file successfully runs and creates backups of our databases.

Scheduled Tasks

We obviously want to keep backing up our databases every night in case something goes wrong.  We’ll create two scheduled tasks.  One that runs ocsbackup.bat for our global, pool, and machine specific information.  And the other that runs our SQL Backups.

I am launching the Task Scheduler from Server Manager (I am using Server 2008 but you can access Task Scheduler on Windows 2003 by going to Control Panel).

Create a Basic Task and give it a name.  We’ll name this OCS Backup.  Click Next to Continue.

Specify how often you want the task to run.  I typically run it Daily. Utilize whatever method works best for your organization. Click Next to Continue.

Choose what time the Daily Task will run.  Again, choose whatever time works best for your organization. Click Next to Continue.

We’ll want to run the script.  Because of this, choose “Start a program.” Click Next to Continue.

Specify the path to our batch file. Click Next to Continue. Review the Settings and then Click Finish.

You can then forcefully run the Scheduled Task to ensure it runs.

Now don’t forget to create the second scheduled task to run the batch file for SQL Backups!

Your OCSBackup folder should look something like this after your scheduled tasks run and your data is backed up.

Backing up your data to a remote Backup Server

Now what good is having all this data backed up onto the OCS File System if OCS crashes?  No good!  We’ll still want to take your backup system and back up all these files including the OCS Standard Edition File Shares.  Now keep in mind that you will want to back up all of these files at some time after your batch files are set to run in Scheduled Tasks.  For example, my Scheduled Tasks are set to run at 8pm.  The batch files do not take long to run.  You can have your backup set to run at 8:30pm or 9:00pm.  Be sure to test and validate this is working as intended and you are getting successful backups.

The Standard Edition File Shares you will want to backup include:

So to sum it up, you will want back up all the above file locations and your OCSBackup folder.  Backing up your Reports folder is optional. But again, keep in mind you will want to run this file level backup after all your Scheduled Tasks are successfully run.