Actian Zen SQL Database Backup Script – Made Easy!

I recently worked with a client that had a custom-written application that used Actian Zen (previously known as Pervasive SQL) as its back-end database. I wanted to make a backup of the data – and not just a single, manual backup, but a nightly, automated backup. I developed a script that works perfectly for what I need – below is the script, along with an explanation of it. It’s made for Windows Server 2012, although it should work the same for Windows Server 2008 as well. Hope it helps!

Contents

• What It Does
• The Pervasive SQL Backup Script
• Setting Up the Pervasive SQL Backup Script
• Backup Script Explanation

What It Does

This script does the following:

• Pauses the database, using the Actian Zen Backup Agent
• Makes a new folder named with today’s date and time, so that backups can be easily sorted
• Makes a backup of the .mkd and .ddf files, which are the recommended files to back up according to Goldstar Software
• Unpauses the database, allowing it to return to normal use and roll in the delta files
• Deletes backup folders older than a specified age, so that your backup directory does not fill up

The Actian Zen SQL Backup Script

Let’s cut right to the chase with the script itself. You can also move on to the setup section below to find out how to use it, as well as the explanation section, which I really suggest before implementing this solution in production.

Download the Script – Rename .txt to .bat

@ECHO off
::Set the variables
set dataLocation=C:\PervasiveSQLDBData\
set backupLocation=D:\Database_Backup\
set daysToKeepBackups=10
set installLocation=C:\Program Files (x86)\Pervasive Software\PSQL\PBA\BIN\

:::Set the date/time variables
::Time, from http://stackoverflow.com/questions/1192476/format-date-and-time-in-a-windows-batch-script
set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%
set secs=%time:~6,2%
if "%secs:~0,1%" == " " set secs=0%secs:~1,1%
 
::Date, from http://stackoverflow.com/a/3475477/774359
for /f "tokens=1-4 delims=/-. " %%i in ('date /t') do (call :set_date %%i %%j %%k %%l)
goto :end_set_date
 
:set_date
if "%1:~0,1%" gtr "9" shift
for /f "skip=1 tokens=2-4 delims=(-)" %%m in ('echo,^|date') do (set %%m=%1&set %%n=%2&set %%o=%3)
goto :eof
:end_set_date
 
 
::Create the datetime string and create a folder with that name
set datetime=%yy%_%mm%_%dd% %hour%_%min%_%secs%
mkdir "%backupLocation%\%datetime%"


::Put the database in Continuous Operations Mode
cd %installLocation%
pvbackup -on

echo.
echo Copying files:
echo ---------------


::Copy all .mkd files
xcopy "%dataLocation%*.mkd" "%backupLocation%%datetime%" /y           
::Copy all .ddf files
xcopy "%dataLocation%*.ddf" "%backupLocation%%datetime%" /y


::Take the database off of Continuous Operations Mode
pvbackup -off

echo.
echo Deleting files:
echo ---------------

::Delete backup folders that are older than 10 days
ForFiles /P %backupLocation% /D -%daysToKeepBackups% /C "CMD /C if @ISDIR==TRUE rmdir /Q /S @FILE"
::Alternative: delete folders that aren't named "importantFolder" - useful if you have a ".sync" or ".dropbox" folder used by another program to index the backup folder
::ForFiles /P %backupLocation% /D -%daysToKeepBackups% /C "CMD /C if @ISDIR==TRUE rmdir /Q /S @FILE|find /v /i ""importantFolder"""

Setting Up the Actian ZenSQL Backup Script

There are a few things you’ll need in order to set up this script. The first is Actian Zen SQL installed and running – I assume you have that already. The second thing you’ll need to do is install the Actian ZenBackup Agent. Which one you need will depend on your license – my server currently has database version 3.2, 32 bit installed, so I therefore went to the “Backup Agent v3.2 Downloads” section and found the “Backup Agent v3.2 – Windows” row in the table, and then clicked on the “32-bit” link to download it. After installing it, I was able to use the pvbackup command. If is installed correctly, running pvbackup from the command line should show you some copyright information as well as usage information – if it’s installed incorrectly, it will tell you that “pvbackup is not a recognized command”.

Installed correctly

Installed incorrectly

The next thing you’re going to want to do is modify the batch file. The explanation for why I wrote it this way is below, and is necessary reading to make sure you don’t mess up your existing installation – this is more of a quick guide for setting up the script.

• Change the location of your Actian Zen SQL DB Data
  • My server has the Actian Zen SQL DB data stored in C:\PervasiveSQLDBData\. Since this most likely is not where your data is, you will have to change the variable on line 3. Remember – the data location is the folder in which all of your .mkd and .ddf files reside.
• Change the location where you’ll be storing your backup
  • In my server setup, the OS and programs are stored on the C:\ drive, which is an SSD. There is also a much larger hard drive, which is the D:\ drive. I store all of my database backups in D:\Database_Backup. If you intend to store your backup files elsewhere, you will have to change the variable on line 4.
• (Optional) Change the how long backups stick around
  • At the end of my script, I remove any folders in this backup location that are older than 10 days. If you would like to change how long old backups will remain in this folder, you will need to change the variable on line 5.
• (Optional) Change the location of your Actian Zen Backup Agent installation
  • My script uses C:\Program Files (x86)\Pervasive Software\PSQL\PBA\BIN\ as its installation location. If you installed the Actian Zen Backup Agent in a different place, you’ll need to change this line or the pvbackup -on and other pvbackup commands will fail. If this is not the correct location for you, you will need to change line 6. Note that if you installed the 64-bit version of Actian Zen Backup Agent, the “(x86)” will need to be removed from this path.
• (Optional) Set up a scheduled task to run the script automatically
  • Once you finish setting everything else up, you can then double click the file or launch it from the command line, and it will execute. This is useful to do manually if you want to create a backup right before doing something stupid innovative. If you’re not 100% sure what your changes will do, just double click and make a backup, and the worst that happens is you’ll have to delete the real files and copy over your backed up files. But here’s the real question – do you really want to be logging in to your server and running this backup constantly? Of course not! Instead, you can set up a simple task in the Windows Task Scheduler to run the .bat file as often as you’d like. I have mine running at 2AM every night currently, backing up that day’s changes. However, because of the Continuous Operations Mode mentioned below, you can actually back up the database while it’s still in use – hourly if you want, or even more often if you’re particularly adventerous. You can find a quick guide on setting up a basic Task Scheduler task here – remember to name the task something nice so you’ll be able to recognize it when you want to make changes later. Also note that you’ll need to put the batch file in a specific location and not move it, otherwise you’ll have to modify the scheduled task to point to the batch file’s new location.

Backup Script Explanation

Now for the fun part – an explanation of what each section of the script does.

@ECHO off
::Set the backup location
set dataLocation=C:\PervasiveSQLDBData\
set backupLocation=D:\Database_Backup\
set daysToKeepBackups=10
set installLocation=C:\Program Files (x86)\Pervasive Software\PSQL\PBA\BIN\

This section is relatively simple – first, we turn @ECHO off, which basically makes the command prompt not show us all of the commands as they run, but instead only show us the output of the commands. Removing this line can help a lot when it comes to debugging the script, should it fail for some reason. The next lines beginning with set are setting variables, which will be used throughout the script, but are set in one convenient place at the top.

:::Set the date/time variables
::Time, from http://stackoverflow.com/questions/1192476/format-date-and-time-in-a-windows-batch-script
set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%
set secs=%time:~6,2%
if "%secs:~0,1%" == " " set secs=0%secs:~1,1%
 
::Date, from http://stackoverflow.com/a/3475477/774359
for /f "tokens=1-4 delims=/-. " %%i in ('date /t') do (call :set_date %%i %%j %%k %%l)
goto :end_set_date
 
:set_date
if "%1:~0,1%" gtr "9" shift
for /f "skip=1 tokens=2-4 delims=(-)" %%m in ('echo,^|date') do (set %%m=%1&set %%n=%2&set %%o=%3)
goto :eof
:end_set_date
 
 
::Create the datetime string and create a folder with that name
set datetime=%yy%_%mm%_%dd% %hour%_%min%_%secs%
mkdir "%backupLocation%\%datetime%"

This is a pretty confusing code block, which uses a number of system calls as well as string manipulation to get to a fairly simple result – the ability to use variables for year, month, day, hours, seconds, and minutes – in order to set up a datetime variable, which is then used to create a folder using that datetime variable in the backup location. This allows for a consistent naming scheme for folders, inside which we will soon put the backed-up files. I got a lot of help crafting these code blocks from two StackOverflow answers, whose links I put in comments in the code for future reference as well as because I like to cite my sources. Note that the time here is based on the server’s local time.

::Put the database in Continuous Operations mode
cd %installLocation%
pvbackup -on

This is the crux of the database backup part of the script – the pvbackup -on command puts the database in “Continuous Operations Mode”. You can find more information about it here – basically, when the database is in use, it writes the data to the .mkd files. However, we want to copy the .mkd files as a backup – this poses a problem. How can we copy a file that is actively being changed? Actian Zenmakes this easy: just put the database into Continuous Operations Mode. What that does is lock the .mkd files so that they will not be changed, allowing us to copy them. This does not, however, stop the database from being useable – instead, all changes are written to delta files, which use the .^^^ file extension. This does have some performance impact, though, so I wouldn’t suggest leaving Continuous Operations Mode on for long – at the end of the script, we turn it off. Note that if for some reason the script fails midway through, you may have to run pvbackup -off manually to turn it off. If it’s already off, running that command will not hurt anything. You can also run pvbackup -status to find out whether it’s currently on or off.

::Copy all .mkd files
xcopy "%dataLocation%*.mkd" "%backupLocation%%datetime%" /y           
::Copy all .ddf files
xcopy "%dataLocation%*.ddf" "%backupLocation%%datetime%" /y

These lines simply copy the .mkd and .ddf files (database and data definition files) from the data location and put them into the backup location, specifically into the folder named by the date and time calculated above.

::Take the database off of Continuous Operations Mode
pvbackup -off

Very important – we need to turn Continuous Operations Mode off. This will roll all of the delta files back into the main database in the background – again, because your database is doing more things, you’ll have a slight performance impact, but unless you’re in a high-performance environment you probably won’t notice at all. Once all of the delta files are rolled back into the database, everything will be back to normal.

::Delete backup folders that are older than 10 days
ForFiles /P %backupLocation% /D -%daysToKeepBackups% /C "CMD /C if @ISDIR==TRUE rmdir /Q /S @FILE"

One issue with using backup scripts that don’t overwrite old backups is that they can grow out of control pretty quickly. If you run this backup every hour, and your database is 1GB, then your backup will be growing by 24GB/day. You can do some complex compression if your data is relatively similar day-to-day, but it doesn’t look like Actian Zen SQL offers any kind of differential backup natively (which would provide you with the only the changes since the last backup). Because of that, I chose to simply create a new backup every time the script ran – which brings you to the problem of having way too many backups after some time. To mitigate this issue, every time the script runs, it simply deletes backups older than a certain number of days (based on the Date Modified file property). Personally, I have this set to 10 – based on my use case, I will never need a backup older than 10 days. Depending on your use case, even having more then a single copy may be overkill – but as long as you have the space to store the backups, you might as well. Note that the if @ISDIR==TRUE part of the code checks to make sure that what you’re deleting is a directory – it will not delete other files in the backups directory, so that might be a good spot to put the backup script.

::Alternative: delete folders that aren't named "importantFolder" - useful if you have a ".sync" or ".dropbox" folder used by another program to index the backup folder
::ForFiles /P %backupLocation% /D -%daysToKeepBackups% /C "CMD /C if @ISDIR==TRUE rmdir /Q /S @FILE|find /v /i ""importantFolder"""

Ideally, you wouldn’t have anything in your backup folder besides the folders that this script creates, and possibly the script file itself. However, I ran into a problem using BitTorrent Sync to synchronize these backups to an offsite location (a process which I describe in this post, using Resilio Sync as my synchronization software). The problem is that BitTorrent Sync creates a folder named .sync, which is used to store the index it builds of the directory. Deleting that .sync folder causes the program to display an error, and syncing of that folder stops completely. The solution? Just delete folders older than a certain age, but ignore the .sync folder completely, leaving it there permanently. To do that specifically, replace importantFolder with .sync. Works with any other single folder as well. Credit to user “Magoo” on StackOverflow for helping me with the string comparison, which is much more difficult than I would have expected.

That’s it! I’ve had this script running for a few weeks now with no issues. Have any questions or comments? Let me know below!