RSS Subscription 167 Posts and 2,643 Comments

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.

lcscmd /config /action:export /level:global,pool /configfile:<drive>:\<path>\<filename>.xml /poolname:[name of Standard Edition server, which is used for the pool name]

lcscmd /config /action:export /level:machine /configfile: <drive>:\<path>\<filename>.xml /fqdn:[FQDN of server from which settings are to be exported]

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:

“C:\Program Files\Common Files\Microsoft Office Communications Server 2007 R2\LCSCmd.exe” /config /action:export /level:global,pool /configfile:C:\OCSBackup\SHUD-OCSFE01_GlobalPool_Backup.xml /poolname:SHUD-OCSFE01

“C:\Program Files\Common Files\Microsoft Office Communications Server 2007 R2\LCSCmd.exe” /config /action:export /level:machine /configfile:C:\OCSBackup\SHUD-OCSFE01_Machine_Backup.xml /fqdn:SHUD-OCSFE01.shudnow.net

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:

“C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\sqlcmd.exe” -S.\RTC -i “c:\OCSBackup\ocssqlbackup.sql”

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:

“C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\sqlcmd.exe” -S .\RTC -i c:\ocsbackup\expressmaint.sql

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.

Share

13 Responses to “OCS 2007 R2 Standard Edition Front End Automated Backups”

  1. on 11 Feb 2010 at 10:50 pmKGB

    Thanks for this.

    Kyle

  2. on 26 Feb 2010 at 4:35 pmSID

    Thanks

  3. on 05 Mar 2010 at 10:50 amPetri

    Thanks, great post. Can you also write post about restoring SE front-end?

    Btw, is there little spelling mistake in file names here:
    "You take this code and save it as storemaint.sql. Then run the following code:
    “C:Program Files (x86)Microsoft SQL Server90ToolsBinnsqlcmd.exe” -S .RTC -i c:ocsbackupexpressmaint.sql"

    Should the file name storemaint.sql be expressmaint.sql?

  4. on 05 Mar 2010 at 10:50 amPetri

    Thanks, great post. Can you also write post about restoring SE front-end?

    Btw, is there little spelling mistake in file names here:
    "You take this code and save it as storemaint.sql. Then run the following code:
    “C:Program Files (x86)Microsoft SQL Server90ToolsBinnsqlcmd.exe” -S .RTC -i c:ocsbackupexpressmaint.sql"

    Should the file name storemaint.sql be expressmaint.sql?

  5. on 06 May 2010 at 7:00 pmPawel

    Many thanks for great article, much appreciated!!
    my $0.02

    you can, if you wish to, split Global Config and Pool Config backup to two separate xml files:
    If you prefer to back up each of the two levels of settings to a separate configuration file (.xml file), run the command two times, with the following modifications:
    •For the /level attribute, specify only one of the two setting levels (global or pool) each time you run the command.
    •For the /configfile attribute, specify a different, unique file name for each level.

    I use ExpressMaint utility (found it thanks to you!) with command line switches for SQL dbs backup http://www.sqldbatips.com/showarticle.asp?ID=29

    I've placed service account for that scheduled task into RTCUniversalReadOnlyAdmins group (so config backup runs ok) and local Administrators group on OCS box (so SQL dbs backup can run). Is that correct?

  6. on 14 May 2010 at 2:05 ameshudnow

    Looks correct.

  7. on 21 Oct 2010 at 2:34 amNancy Jiang

    Of course, it should look like:

    @backupfldr = ‘c:OCSBackup’,
    @reportfldr = ‘c:TempRapporter’,

    Nancy

  8. on 21 Oct 2010 at 2:36 amNancy Jiang

    The back slash keeps truncating when it's published. Any way, you know what I try to show you. Thanks!

  9. on 26 Apr 2011 at 7:33 amJMS

    I'm having the same troubles as Nancy Jiang. Label c has already been declared. Any ideas about why that happens?

  10. on 26 Apr 2011 at 7:44 amJMS

    I also get an error for line 2 and line 15 for:
    database = 'ALL_USER'

  11. on 26 Apr 2011 at 8:15 amJMS

    And here is the fix for the above instructions. You placed single quotes around the variables. They need to be double quotes. The below example might be truncated by the website poasting.

    exec expressmaint
    @database = "ALL_USER",
    @optype = "DB",
    @backupfldr = "c:ocsbackup",
    @reportfldr = "c:
    eports",
    @verify = 1,
    @dbretainunit = "days",
    @dbretainval = 1,
    @rptretainunit = "weeks",
    @rptretainval = 1,
    @report = 1

  12. on 14 Aug 2013 at 8:34 amJim

    Sorry to dredge this up, but the reference to "SQLDBATips has the vbscript code in order to do that here. " appears to be broken. Where can I get the vbscript code to create the expressmaint sproc?

  13. on 16 Aug 2013 at 6:58 amElan Shudnow

    Sorry, I never saved it. Your best bet is to contact the author of the site that was hosting the script file and ask for it.

Trackback this post | Feed on Comments to this post

Leave a Reply