Home » Java » Enterprise Java » Derby Database Backup

About Michael Remijan

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.

Derby Database Backup

Abstract

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.

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.

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.

(0 rating, 0 votes)
You need to be a registered member to rate this.
1 Comment Views Tweet it!
Do you want to know how to develop your skillset to become a Java Rockstar?
Subscribe to our newsletter to start Rocking right now!
To get you started we give you our best selling eBooks for FREE!
1. JPA Mini Book
2. JVM Troubleshooting Guide
3. JUnit Tutorial for Unit Testing
4. Java Annotations Tutorial
5. Java Interview Questions
6. Spring Interview Questions
7. Android UI Design
and many more ....
I agree to the Terms and Privacy Policy

1
Leave a Reply

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
Nick Patel Recent comment authors

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

  Subscribe  
newest oldest most voted
Notify of
Nick Patel
Guest
Nick Patel

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.