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.
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.
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.
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.
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
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.
Backing up a Derby database is pretty easy. Just call
Backing Up a Database. (2013, January 24). db.apache.org. Retrieved from https://db.apache.org/derby/docs/10.0/manuals/admin/hubprnt43.html.