Importing and exporting data
Derby Server and Administration Guide
66
By using the full backup copy, archived logs, and active logs, you can restore a database
to its most recent state by performing roll-forward recovery. You perform a roll-forward
recovery by specifying the connection URL attribute rollForwardRecoveryFrom=path
at boot time. This brings the database to its most recent state by using full backup
copy, archived logs, and active logs. All the log files should be in the database log path
directory.
For more information, see "rollForwardRecoveryFrom=path attribute" in the Derby
Reference Manual.
Backing up a database:
In the following example, a database named wombat is backed up to the d:/backup
directory with log archive mode enabled:
connect 'jdbc:derby:wombat;create=true';
create table t1(a int not null primary key);
------------------DML/DDL Operations
CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE
('d:/backup', 0);
insert into t1 values(19);
create table t2(a int);
-----------------DML/DDL Operations
-----------------Database Crashed (Media Corruption on data disks)
Restoring a database using roll-forward recovery:
In the following example, the database is restored using roll-forward recovery after a
media failure:
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=d:/backup/wombat';
select * from t1;
---------------DML/DDL Operations
After a database is restored from full backup, transactions from the online archived logs
and active logs are replayed.
Importing and exporting data
You can import and export large amounts of data between files and the Derby database.
Instead of having to use INSERT and SELECT statements, you can use Derby system
procedures to import data directly from files into tables and to export data from tables into
files.
The Derby system procedures import and export data in delimited data file format.
· Use the export system procedures to write data from a database to one or more
files that are stored outside of the database. You can use a procedure to export
data from a table into a file or export data from a SELECT statement result into a
file.
· Use the import system procedures to import data from a file into a table. If the target
table already contains data, you can replace or append to the existing data.
Methods for running the import and export procedures
You can run the import and export procedures from within an SQL statement using
ij
or
any Java application.
The import and export procedures read and write text files, and if you use an external file
when you import or export data, you can also import and export blob data. The import
procedures do not support read-once streams (live data feeds), because the procedures
