TECH TIP: Automating SQL Server Backups with Wonderware Historian 2014 R2 and Later

Over the past couple of years, we have upgraded several Wonderware Historian systems and ran into an issue which others may have run into as well.  Prior to Historian 2014 R2 (released in 2017), Wonderware Historian was packaged with MS SQL Server Standard.  SQL Server Standard allowed for the ability to schedule maintenance procedures such as database backups.  Post 2014 R2, the Wonderware Historian came packaged with SQL Server Express which lacks maintenance plans.

OK – no big deal – this should be easy to figure out.  A quick Google search lead to this article from Microsoft which reviews the steps and utilities required to automate database backups with MS SQL Express. While this approach works, it is quite involved.  The process involves:

1. Creating a stored procedure to backup your database
2. Downloading the SQLCMD utility from Microsoft
3. Creating a batch file using a text editor
4. Using the Windows Task Scheduler to execute the batch file

While this process is not terribly difficult, it does add complexity to the long-term maintenance of the system.  The process needs to be documented and makes future upgrades or migrations more difficult.

Our solution was to take advantage of the Event System within the Wonderware Historian which provides the ability to setup the detection of an event and execute an action when the event occurs.  In our case, we will use the ‘schedule’ detector type and a ‘generic SQL’ action type.

Here are the steps to create a backup event:

1. Create the same stored procedure to backup your database as you would have according to the Microsoft article referenced above.
2. Create an event tag within the configuration editor of the Historian management console:
   • Create a new event tag and give it a unique name. Click next to configure the description.
   • Click next and on the ‘Detector’ window choose the ‘Schedule’ detector type and configure the recurring time which you would like to schedule the backups.
   • Click Next and on the ‘Action’ window and choose ‘Generic SQL.’ Enter the command to execute the stored procedure along with parameters as required: ‘EXEC sp_BackupDatabases @backupLocation = ‘[Path to Backup Directory]’, @databaseName = ‘[Database Name]’, @backupType = ‘[Backup Switch]’

The advantages to this solution are:

• No need to download any Microsoft utilities.
• No need to maintain batch files or use the Windows Task Scheduler.
• The Historian event tag is maintained within the Historian configuration, making documentation, system backups, and migration to future servers an easier task.

Expertise in Integrating and Deploying AVEVA® Software

As a certified AVEVA system integrator, NeoMatrix has a committed a team of certified industrial automation engineers to consult and deliver solutions leveraging the AVEVA platforms (formerly Wonderware).