Derby Database Backup

Abstract

I have already posted a number of blogs about Derby:

• Multiple Derby Network Servers on the same Host
• Apache Derby Database Users and Permissions
• Integration Testing with Maven and an In-Memory Derby Database

This wasn’t intended to be a series. But over the years I’ve been using Derby more and more. Recently, I started using Derby as my database of choice for my Microservice architecture. These are personal-use applications, so Derby is more than sufficient. Even though these are personal-use applications, I require multiple servers with limited user permissions and - most importantly - backup. I’d hate to lose my data! The purpose of this post is to demonstrate how to backup a Derby database.

Disclaimer

This post is solely informative. Critically think before using any information presented. Learn from it but ultimately make your own decisions at your own risk.

Requirements

I did all of the work for this post using the following major technologies. You may be able to do the same thing with different technologies or versions, but no guarantees.

• Apache Derby 10.14.2.0
• OpenJDK 64-Bit Server VM Zulu11.1+23 (build 11-ea+22, mixed mode)

Download

There are no downloads with this blog post. The scripts are shown in full.

Derby System Utility

Backing up a Derby database is really quite simple. Derby has a built-in system utility for performing the backup. The utility is SYSCS_UTIL.SYSCS_BACKUP_DATABASE('/location/of/the/backup/'). When called, Derby will lock the database and perform the copy operation to the file system location you specify as the parameter to SYSCS_BACKUP_DATABASE. Now that we know the system utility to do the backup, let’s look at a bash script to automate it.

Backup Script

Listing 1 is a bash script which can be easily modified to backup any Derby database on any network server.

Listing 1 - derby-mydatabase-backup.sh

#!/bin/bash

# Define a bunch of variables which will be used within this script.
# The names of the variables should be self-explanatory.
DERBY_HOME=/opt/db-derby-10.14.2.0-bin/
NETWORK_SERVER_HOST=localhost
NETWORK_SERVER_PORT=1527
DATABASE_NAME=mydatabase
DATABASE_USER=sa
DATABASE_PASSWORD=abc123
JDBC_URL="jdbc:derby://$NETWORK_SERVER_HOST:$NETWORK_SERVER_PORT/$DATABASE_NAME"
BACKUP_DIRECTORY="/tmp/$DATABASE_NAME-backup/$NETWORK_SERVER_PORT"
BACKUP_SCRIPT="$BACKUP_DIRECTORY/backup.sql"

# Remove old backup if it exists. It is not a good idea to
# perform a backup on top of an existing backup.
rm -rf $BACKUP_DIRECTORY
mkdir -p $BACKUP_DIRECTORY
cd $BACKUP_DIRECTORY

# Use the `echo` command to dynamically create an SQL file.
# This SQL file will be used by Derby `ij` to connect to
# the database and perform the backup.
echo "connect '$JDBC_URL' user '$DATABASE_USER' password '$DATABASE_PASSWORD';" >> $BACKUP_SCRIPT
echo "CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('$BACKUP_DIRECTORY');" >> $BACKUP_SCRIPT
echo "exit;" >> $BACKUP_SCRIPT

# Run the Derby `ij` application, passing it the SQL file
# that was just dynamically created. `ij` will read the 
# SQL file, executing its commands. This will then
# cause `ij` to connect to the database and call the 
# system utility to perform the backup.
$DERBY_HOME/bin/ij $BACKUP_SCRIPT

Let’s take a look at this script in more detail.

Lines 5–15 setup a number of variables used within the script. Some variables are used to set the values of other variables. There is nothing too complicated here. The names of the variables are self-explanatory.

Lines 17–19 is file system maintenance. It is not a good idea to perform a backup on top of an existing backup. So these lines remove an existing backup (if it exists) and creates a new, empty, backup directory.

Lines 24–26 are then responsible for creating the backup.sql script file. This script file contains the SQL commands to perform the backup. Line 24 is the connect command so Derby ij can connect to the database you want to backup. Line 25 is where the magic happens with a call to the SYSCS_BACKUP_DATABASE system utility. The location of the backup directory is passed as a parameter to the utility. When this SQL command is executed, Derby will lock the database and perform the backup. Line 26 is the exit command to exit ij.

Line 33 is then finally where everything happens. The Derby ij command is called with the location of the dynamically created backup.sql file passed to ij as a command-line parameter. When bash executes line 33, and if everything goes well, the Derby database will be backed up.

NOTE If you are running the Derby network server with a Java security policy, you may run into some problems with this script. the Java SecurityManager may prevent the network connection to the database or the SecurityManager my encounter permission problems writing to the backup directory.

Summary

Backing up a Derby database is pretty easy. Just call SYSCS_UTIL.SYSCS_BACKUP_DATABASE('/location/of/the/backup/').

References

Backing Up a Database. (2013, January 24). db.apache.org. Retrieved from https://db.apache.org/derby/docs/10.0/manuals/admin/hubprnt43.html.