Performing Export and Backup of H2 Databases

1. Overview

Although H2 databases were initially developed to function in-memory only, the engine now supports secondary storage and even server modes. Because of this, use cases have also increased, leading to two closely related needs: creating reliable backups for recovery and exporting data for inspection or reuse. Although both move data out of the database, they serve different purposes and rely on different mechanisms. Still, H2 meets these needs through SQL commands directly inside the engine. This way, the database can keep running while maintenance is underway.

In this tutorial, we explain backup and export mechanisms for H2. First, we go through the basic concepts of both actions. After that, we understand backup methods, see why copying database files is unreliable, and how the built-in commands operate while the database is online. Finally, we cover CSV and SQL-based exports and briefly note additional complementary techniques that fit naturally into H2-based workflows.

2. H2 Backup and Export Concepts

Before delving into specifics, it’s important to understand how backups and exports differ for H2. We also consider the environment and operating conditions.

2.1. Backups and Exports

In general, backups preserve entire instances for later restoration. In the case of H2, this means creating a package that includes all tables, any created indices, and metadata. Backups relate to a particular point in time and primarily exist for recovery scenarios, such as data loss or environment rebuilds.

On the other hand, exporting usually focuses on data portability instead of recovery. Usually, an export is in a text-based format such as a CSV file or SQL script. Critically, the data within an export often contains only specific tables or query results. Although we can use exports for analysis and partial migration, they rarely provide the same guarantees as a full backup in terms of content. For instance, an export might only include part of the tables, no metadata, or other limitations.

2.2. Online Operations

Many H2 databases run continuously in embedded mode as part of an application. In these cases, backups and exports should be able to complete successfully while data is being read and written.

To that end, the recommended approaches for the H2 engine rely on SQL commands executed through normal database connections. Thus, the engine itself can ensure consistency instead of relying on external file operations that are unaware of the internal state.

3. Backing Up an Online H2 Database

For offline scenarios where downtime is acceptable, shutting down the database cleanly and then copying files can be a viable option. However, this approach is usually reserved for controlled maintenance windows and isn’t a real substitute for online backups in continuously running systems.

Since uninterrupted operation can be critical in many cases, having a backup solution for an H2 database that’s currently in use can be crucial.

3.1. Database File Copying (Unsafe)

Because, like other database engines, H2 often stores data in files, copying those files may appear to be an easy backup solution. However, when the database is running, pages may be in the middle of an update. Thus, copying files at any given moment can lead to internal inconsistencies, such as partially written rows or mismatched tables, depending on the query.

Another issue is long-term reliability. Unlike standardized languages and formats, the H2 engine database files may change across versions. Due to that, a separate copy of a database file may fail to open after an upgrade, which limits its usefulness as a dependable backup.

3.2. Online Backups With BACKUP TO

To avoid offline backups and address problems related to database files, H2 provides a special command. Specifically, the BACKUP TO SQL statement creates a ZIP archive with a consistent snapshot of the database at a given point in time, even while the database is running:

// JDBC-based online backup
String backupFile =
    "backup-" + java.time.LocalDateTime.now()
      .format(java.time.format.DateTimeFormatter.ofPattern("yyyyMMdd-HHmmss"))
    + ".zip";
try (Connection con = DriverManager.getConnection(
       "jdbc:h2:./db/mydb", "user", "password");
     PreparedStatement ps =
       con.prepareStatement("BACKUP TO '" + backupFile + "'")) {
  ps.executeUpdate();
}

Because the backup is executed by the database engine, it avoids the consistency risks associated with copying filesystem objects. Further, we can run such commands via both plain JDBC or with frameworks such as Spring Data using native queries, keeping backup logic close to application code.

4. Exporting Data From H2

Conversely, exports can be a bit more flexible, since they usually don’t have the rigorous requirements of a backup.

4.1. CSV Exports With CSVWRITE

For data inspection or sharing, the H2 engine supports the CSVWRITE function. It writes the result of an SQL query directly to a CSV file, making it easy to extract data in a widely supported format:

CALL CSVWRITE(
  '<BACKUP_FILE_PATH>',
  'SELECT * FROM <TABLE_NAME>'
);

In this case, since the export is a SELECT statement, it can also include filters, joins, and ordering. This makes CSVWRITE particularly useful for reporting, debugging, and transferring subsets of data to other tools such as spreadsheets or analytics systems.

Importantly, this export is fairly simplistic in that it deals mainly with data and doesn’t really handle metadata in depth.

4.2. Logical Exports With SCRIPT

When both schema and data are needed, the H2 engine command SCRIPT can be used to get a logical export as SQL statements. Just like with other tools that produce SQL statements, these output scripts can recreate tables and insert data when executed later:

SCRIPT SIMPLE TO '<BACKUP_SQL_SCRIPT_PATH>'
TABLE PUBLIC.<TABLE_NAME>;

Unlike CSV exports, SQL scripts preserve structural information. Because of this, migrations, reproducible test setups, and version-controlled snapshots of database state are often facilitated by this export method. For instance, SQL scripts produced by SCRIPT can be restored using RUNSCRIPT, providing a simple logical backup-and-restore loop for development or testing environments.

5. Summary

In this article, we looked at ways to back up or export data from H2 databases.

In summary, online backups rely on the BACKUP TO command to produce consistent, restorable archives without stopping the database. On the other hand, data exports rely on CSVWRITE for portable, query-driven CSV files and SCRIPT for schema-aware SQL output. Together, these tools cover recovery, portability, and inspection needs while avoiding the risks associated with direct file copying.

In conclusion, H2 provides built-in, engine-managed mechanisms for both backups and exports.