Enterprise Java

Derby Database Backup


I have already posted a number of blogs about Derby:

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.

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


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


# Define a bunch of variables which will be used within this script.
# The names of the variables should be self-explanatory.

# Remove old backup if it exists. It is not a good idea to
# perform a backup on top of an existing backup.

# 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 "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.

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.


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


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

Published on Java Code Geeks with permission by Michael Remijan, partner at our JCG program. See the original article here: Derby Database Backup

Opinions expressed by Java Code Geeks contributors are their own.

Michael Remijan

Michael Remijan is a System Architect at the Federal Reserve Bank St. Louis. He is co-author of 'EJB 3 In Action Second', an active blogger in the Java EE community, a Java EE Guardian, and JavaOne presenter. He has developed enterprise systems for B2C and B2B commerce, manufacturing, astronomy, agriculture, telecommunications, national defense, healthcare, and financial areas.
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

1 Comment
Newest Most Voted
Inline Feedbacks
View all comments
Nick Patel
Nick Patel
5 years ago

Derby database backup is nothing but the high level of backup process, but for that, it is very important you have to go with the windows modules installer worker and with that, it can be possible with the latest updates.

Back to top button