Data protection is a critical aspect of application development. Applications that rely on embedded databases, such as the H2 Database Engine, must implement mechanisms to preserve and recover data. Export and backup strategies provide reliable approaches for maintaining data integrity and ensuring continuity in the event of unexpected failures. In this article, database backup and export concepts are examined with practical H2 implementations in Java.
1. Understanding Backups and Exports
Backups and exports are related concepts, but they serve different purposes in database management. A backup refers to a complete and consistent copy of the database at a specific point in time. It is primarily used for disaster recovery and system restoration. An export is the process of extracting database data into a portable format, such as SQL or CSV. It is commonly used for data migration, reporting, or integration with other systems.
The distinction between backups and exports lies in their structure and use cases. Backups typically preserve the entire database in a format that can be restored quickly with minimal transformation. Exports transform data into formats that are easier to read, transfer, or process externally. Backups prioritize recovery and consistency while exports prioritize portability and flexibility.
2. Backup Using BACKUP TO SQL Command
The H2 engine provides a BACKUP command that creates a compressed archive containing a consistent snapshot of the database.
public class BackupService {
public static void backupDatabase(String backupPath) {
try (Connection conn = DatabaseConfig.getConnection(); Statement stmt = conn.createStatement()) {
String sql = "BACKUP TO '" + backupPath + "'";
stmt.execute(sql);
IO.println("Backup completed");
} catch (Exception e) {
}
}
}
This implementation executes the BACKUP TO SQL command using a JDBC statement. The command generates a compressed archive file that represents a consistent snapshot of the database. This method can be executed while the database is running, which makes it suitable for production environments.
3. Export Table Data to CSV Using CSVWRITE
The H2 engine supports exporting query results directly to a CSV file using the CSVWRITE function.
public class CsvExportService {
public static void exportToCsv(String filePath) {
try (Connection conn = DatabaseConfig.getConnection(); Statement stmt = conn.createStatement()) {
String sql = "CALL CSVWRITE('" + filePath + "', 'SELECT * FROM users')";
stmt.execute(sql);
IO.println("CSV export completed");
} catch (Exception e) {
e.printStackTrace();
}
}
}
This class uses the CSVWRITE function to export table data into a CSV file. The SQL query inside the function defines which data is written. The resulting file is suitable for reporting, data exchange, or external processing.
4. Export Schema and Data Using SCRIPT Command
When both schema and data are required, the SCRIPT command can be used to generate SQL statements.
public class ScriptExportService {
public static void exportToSqlScript(String filePath) {
try (Connection conn = DatabaseConfig.getConnection(); Statement stmt = conn.createStatement()) {
String sql = "SCRIPT SIMPLE TO '" + filePath + "' TABLE PUBLIC.USERS";
stmt.execute(sql);
IO.println("SQL script export completed");
} catch (Exception e) {
e.printStackTrace();
}
}
}
This implementation generates a SQL script that includes both the table structure and its data. The SIMPLE option produces a clean and readable script. This format is useful for migration and version control purposes.
The main class below ties all components together and demonstrates the full workflow.
public class H2BackupDemo {
public static void main(String[] args) {
DataInitializer.initialize();
BackupService.backupDatabase("./backup/database.zip");
CsvExportService.exportToCsv("./backup/users.csv");
ScriptExportService.exportToSqlScript("./backup/users.sql");
}
}
This class initializes sample data and executes backup and export operations.
Database Configuration
public class DatabaseConfig {
private static final String JDBC_URL = "jdbc:h2:./data/sampledb";
private static final String USER = "sa";
private static final String PASSWORD = "";
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
}
}
This class centralizes the database connection logic. The configuration uses a file-based H2 database stored locally.
Sample Table and Data
public class DataInitializer {
public static void initialize() {
try (Connection conn = DatabaseConfig.getConnection();
Statement stmt = conn.createStatement()) {
stmt.execute("CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY, name VARCHAR(100))");
stmt.execute("INSERT INTO users (id, name) VALUES (1, 'Thomas')");
stmt.execute("INSERT INTO users (id, name) VALUES (2, 'Benjamin')");
IO.println("Data created");
} catch (Exception e) {
e.printStackTrace();
}
}
}
This class creates a simple table and inserts sample records.
5. Conclusion
This article presented a guide to performing backups and exports in H2 databases. It explained the conceptual differences between backups and exports and demonstrated practical implementations using built-in H2 commands. By applying these techniques, developers can ensure data safety, improve recovery capabilities, and support seamless data migration.
6. Download the Source Code
This article explored how to perform data export and backup operations with H2 in Java applications.
You can download the full source code of this example here: data export and backup operations with H2 in Java
