apache > db
Apache DB Project
 
Font size:      

Backing Up a Database

Backing Up a Database

Offline Backups

To perform an offline backup of a database, simply use operating system commands to copy the database directory. The database must be shut down prior to performing an offline backup.

For example, on Windows NT, the following operating system command would back up a (closed) database named sample located in d:\mydatabases by copying it to the directory d:\mybackups\2004-06-01:

xcopy d:\mydatabases\sample d:\mybackups\2004-06-01\sample /s /i

(If you are not using Windows NT, substitute the appropriate operating system command for copying a directory and all contents to a new location.)

Note:
On Windows NT, do not attempt to update a database while it is being backed up in this way. Attempting to update a database during an off-line backup will generate a java.io.IOException. Using on-line backups prevent this from occurring.

For large systems, shutting down the database might not be convenient. To back up a database without having to take it off line, system administrators should use on-line backups, described next.

On-line Backups

Use online backups to back up a database while it is booted. During the interval the backup is running, the database can be read, but writes to the database are blocked.

There are two ways to perform on-line backups:

Using the backup procedure

The SYSCS_UTIL.SYSCS_BACKUP_DATABASE() procedure locks the database and performs the copy operation.

The SYSCS_UTIL.SYSCS_BACKUP_DATABASE() procedure takes a string argument representing the location in which to back up the database. Typically you provide the full path to the backup directory. (Relative paths are interpreted as relative to the current directory, not to the derby.system.home directory.)

For example, to specify a backup location of c:/mybackups/2004-06-01 for the currently-open database, you would use the following statement:

CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('c:/mybackups/2004-06-01')

Note:
Use forward slashes as the path separator in SQL commands.

The SYSCS_UTIL.SYSCS_BACKUP_DATABASE() procedure puts the database into a state in which it can be safely copied, then copies the entire original database directory (including data files, on-line transaction log files, and jar files) to the specified backup directory. Files not within the original database directory (for example, derby.properties) are not copied.

Here is an example, which backs up a database to a directory with a name that reflects the current date:

public static void backUpDatabase(Connection conn)throws SQLException
{
String backupdirectory ="c:/mybackups/"+JCalendar.getToday();
CallableStatement cs = conn.prepareCall("CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)"); 
cs.setString(1, backupdirectory);
cs.execute(); 
cs.close();
System.out.println("backed up database to "+backupdirectory);
}

For a database backed up on 2004-06-01, the above commands copy the current database to a directory of the same name in c:/mybackups/2004-06-01.

Uncommitted transactions do not appear in the backed-up database.

Note:
Do not back up different databases with the same name to the same backup directory. If a database of the same name already exists in the backup directory, it is assumed to be an older version and is overwritten.
Note:
If you are doing this through the Network Server, refer to the workaround in Differences between running Derby in embedded mode and using the Network Server.

Using Operating System Commands with the freeze and unfreeze system procedures

Typically, this procedure is used to speed up the copy operation involved in the on-line backup. In this scenario, Derby does not perform the copy operation for you. You use the SYSCS_UTIL.SYSCS_FREEZE_DATABASE() procedure to lock the database, and then explicitly copy the database directory using operating system commands.

For example, because the UNIX tar command uses operating system file-copying routines and Derby uses the IBM Application Developer Kit file-copying routines, tar might provide faster backups than the SYSCS_UTIL.SYSCS_BACKUP_DATABASE procedure.

To use operating system commands for on-line database backups, call the SYSCS_UTIL.SYSCS_FREEZE_DATABASE() system procedure. The SYSCS_UTIL.SYSCS_FREEZE_DATABASE() system procedure puts the database into a state in which it can be safely copied. Once the copy is complete, use the SYSCS_UTIL.SYSCS_FREEZE_DATABASE() system procedure to continue working with the database. Only after SYSCS_UTIL.SYSCS_FREEZE_DATABASE() has been specified can transactions once again write to the database. Read operations can proceed while the database is "frozen".

Note:
To ensure a consistent backup of the database, Derby might block applications that attempt to write to a 'frozen' database until the back up is completed and the SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE()system procedure is called.

The following example demonstrates using freeze and unfreeze surrounding an operating system copy command:

String backupdirectory = "c:/mybackups/" + JCalendar.getToday();
Statement s = conn.createStatement();
s.executeUpdate(
    "CALL SYSCS_UTIL.SYSCS_FREEZE_DATABASE();
//copy the database directory during this interval
s.executeUpdate(
    "CALL SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE();
s.close();
Note:
If you are doing this through the Network Server, refer to the workaround in Differences between running Derby in embedded mode and using the Network Server.

When the Log is in a non-Default Location

Note:
Read Logging on a Separate Device to find out about the default location of the database log.

If you put the database log in a non-default location prior to backing up the database, note the following:

  • If backing up the database using an operating system command, you must explicitly copy the log file as well.
    xcopy d:\mydatabases\sample c:\mybackups\2004-06-01\sample /s /i
    xcopy h:\janet\tourslog\log c:\mybackups\2004-06-01\sample\log /s /i
    
Note:
If you are not using Windows NT, substitute the appropriate operating system command for copying a directory and all contents to a new location.
  • Edit the logDevice entry in service.properties of the database backup so that it points to the correct location for the log. In the above example, the log was moved to the default location for a log, so you could remove the logDevice entry entirely, or leave the logDevice entry as is and wait until the database is restored to edit the entry.

See "Logging on a Separate Device" for information about putting the log in a non-default location.

Backing Up Encrypted Databases

When you back up an encrypted database, both the backup and the log files remain encrypted. To restore an encrypted database, you must know the boot password.


Previous Page
Next Page
Table of Contents
Index