# Using the backup procedures to perform an online backup

Use the `SYSCS_UTIL.SYSCS_BACKUP_DATABASE` procedure or one of the other system backup procedures to perform an online backup of a database to a specified location.

The backup procedures are as follows:

- `SYSCS_UTIL.SYSCS_BACKUP_DATABASE`
- `SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT`
- `SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE`
- `SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT`

Use the `SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE` or `SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT` procedure if you want to make it possible to perform a roll-forward recovery of a damaged database. See Roll-forward recovery for details.

The `NOWAIT` versions of the procedures do not wait for transactions in progress with unlogged operations to complete before proceeding with the backup; instead, they return an error immediately.

See the Derby Reference Manual for details about these system procedures.

All four of these system procedures take a string argument that represents 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/2012-04-01` for a database that is currently open, use the following statement (forward slashes are used as path separators in SQL commands):

    CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('c:/mybackups/2012-04-01')

The `SYSCS_UTIL.SYSCS_BACKUP_DATABASE` or `SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT` procedure puts the database into a state in which it can be safely copied. The procedure then copies the entire original database directory (including data files, online transaction log files, and jar files) to the specified backup directory. Files that are not within the original database directory (for example, `derby.properties`) are not copied. With the exception of a few cases mentioned in Unlogged Operations, the procedure does not block concurrent transactions at any time.

A backup made with the `SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE` or `SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT` procedure is not a full copy of the database, but depends on the log files created in the database since the backup. An attempt to access the backup directly will invalidate the backup. The result could include a corrupted database, missing data, errors during a subsequent attempt at restoring the database, or database corruption errors encountered only once the restored database is being used. The only supported way to access this kind of backup is to restore the database as documented in Roll-forward recovery.

The following example shows how to back up a database to a directory with a name that reflects the current date:

    public static void backUpDatabase(Connection conn)
            throws SQLException {
        // Get today's date as a string:
        java.text.SimpleDateFormat todaysDate =
            new java.text.SimpleDateFormat("yyyy-MM-dd");
        String backupdirectory = "c:/mybackups/" +
            todaysDate.format((java.util.Calendar.getInstance()).getTime());

        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 that was backed up on 2012-04-01, the previous commands copy the current database to a directory of the same name in `c:/mybackups/2012-04-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.

## Unlogged Operations

For some operations, Derby does not log because it can keep the database consistent without logging the data.

The `SYSCS_UTIL.SYSCS_BACKUP_DATABASE` procedure will issue an error if there are any unlogged operations in the same transaction as the backup procedure.

If any unlogged operations are in progress in other transactions in the system when the backup starts, this procedure will block until those transactions are complete before performing the backup.

Derby automatically converts unlogged operations to logged mode if they are started while the backup is in progress (except operations that maintain application jar files in the database). Procedures to install, replace, and remove jar files in a database are blocked while the backup is in progress.

If you do not want backup to block until unlogged operations in other transactions are complete, use the `SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT` procedure. This procedure issues an error immediately at the start of the backup if there are any transactions in progress with unlogged operations, instead of waiting for those transactions to complete.

Unlogged operations include:

- Index creation.

  Only CREATE INDEX is logged, not all the data inserts into the index. The reason inserts into the index are not logged is that if there is a failure, it will just drop the index.

  If you create an index when the backup is in progress, it will be slower, because it has to be logged.

  Foreign keys and primary keys create backing indexes. Adding those keys to an existing table with data will also run slower.

- Importing to an empty table or replacing all the data in a table.

  In this case also, data inserts into the table are not logged. Internally, Derby creates a new table for the import, changes the catalogs to point to the new table, and drops the original table when the import completes.

  If you perform such an import operation when backup is in progress, it will be slower because data is logged.

Parent topic: Online backups
Related concepts
Using operating system commands with the freeze and unfreeze system procedures to perform an online backup