Java Quiz Player

Backing up a Java DB Database

Mar 26, 2013

This blog post is about making a backup copy of a Java DB database.

1. Java DB

Java DB is a relational database management system that is based on the Java programming language and SQL. This is the Oracle release of the Apache Software Foundation's open source Derby project. Java DB is included in the Java SE 7 SDK.

Java DB provides a way to backup a database while it is online or offline.

A Java DB database is stored in files within a directory with the same name as that of the database. The backup database is stored at a specified location. The database can be restored from a backup copy.

2. Online Backups

Online backups are used to backup while a database is running. This process does not block transactions in progress.

Online backups can be made using several types of backup procedures or by using operating system commands with the freeze and unfreeze system procedures.

2.1. SYSCS_UTIL.SYSCS_BACKUP_DATABASE Backup Procedure

The built-in SYSCS_UTIL.SYSCS_BACKUP_DATABASE stored procedure takes a VARCHAR argument that represents the location in which to backup the database. The procedure does not return a result.

For example, to specify a backup location of d:\dbbackups\ for a database that is currently open, use the following statement: CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('d:/dbbackups/'). NOTE: (a) An absolute path is recommended to specify the location. (b) A forward slash can be used as a path separator.

This procedure puts the database into a state in which it can be safely copied. The procedure then copies the entire original database directory.

The following is an example Java method to backup a database to a directory using JDBC:

/*
 * The method parameter is the Connection object for the database
 * being backed up. The CallableStatement interface is used to
 * execute SQL stored procedure.
 */
public static void backUpDatabase(Connection conn)
		throws SQLException {
	String backupDirectory = "d:/dbbackups/";
	String sql = "CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)";
	CallableStatement cs = conn.prepareCall(sql);
	cs.setString(1, backupDirectory);
	cs.execute();
	cs.close();
}

This copies the current database to a directory with the same name in d:\dbbackups\. Note that any uncommitted transactions do not appear in the backed up database.

2.2. Backup Interactively with 'ij' Tool

ij is a command line tool included with Java DB. ij is a JDBC tool used to run interactive queries on a Java DB database.

Start ij, connect to the database and execute the backup procedure:

os prompt> ij 
ij> CONNECT 'jdbc:derby:sampleDb';
ij> CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('d:\dbbackups\');

The string argument 'd:\dbbackups\' to the procedure represents the directory in which to backup the sampleDb database. The database is stored within the specified directory (creates the directory if it does not exist).

2.3. Variants of Built-in Backup Procedures

3. Offline Backups

The database is backed up using an operating system command. This makes a copy of the database directory. The database must be shut down prior to performing an offline backup.

On Windows, the operating system command xcopy (copies files and directory trees) makes a backup of the database. For example, a database with the name sampleDb and located in d:\databases is copied to the directory d:\dbbackups\, using the following:

os prompt> xcopy d:\databases\sampleDb d:\dbbackups\sampleDb /s /i

4. Example Usage of Online Backup

Online backups are useful when shutting down and restarting a database is not convenient. An example usage is a Java Swing based desktop application that uses a database. The application has a function to make an online backup, while the database is running. The following is an outline of the function:

5. References

Return to top