How to Export H2 Database Schema and Data as SQL Script

Mar 26, 2026

Problem

I needed to migrate my H2 database from one environment to another. The database had several tables with data, and I wanted a way to export everything so I could recreate the same database state on a different machine.

At first, I tried exporting to CSV files. I could get the data out, but I had to manually recreate the table structures. This became tedious when I had many tables with complex schemas.

What I really needed was a way to export both the table definitions and the data together—something that would let me restore the entire database with a single command.

Purpose

I wanted to find a simple method to:

Export the complete database schema (CREATE TABLE statements)
Export all the data (INSERT statements)
Restore everything easily on another system
Keep the database state in version control

Trial and Error

Attempt 1: CSV Export

I started by trying to export each table to CSV:

CALL CSVWRITE('/tmp/users.csv', 'SELECT * FROM users');

This worked for the data, but I had no schema information. I would need to write separate CREATE TABLE scripts for every table. This was not scalable.

Attempt 2: Using the SCRIPT Command

I looked at the H2 documentation and found the SCRIPT command. It exports the database as SQL statements—both the schema and the data.

SCRIPT TO '/tmp/full-export.sql';

This was exactly what I needed. The output file contained:

CREATE TABLE statements for all tables
INSERT statements for all data
CREATE INDEX statements
Other schema-related SQL

Attempt 3: Simplified Export for Specific Tables

For some migrations, I only needed specific tables. The SCRIPT command supports this:

SCRIPT SIMPLE TO '/tmp/users-export.sql'
TABLE PUBLIC.users;

The SIMPLE option makes the output more readable by using simpler INSERT statements.

Solution

The SCRIPT command in H2 exports both schema and data as SQL statements. Here’s the basic usage:

Export the entire database:

SCRIPT TO '/path/to/full-export.sql';

Export a specific table:

SCRIPT SIMPLE TO '/path/to/users-export.sql'
TABLE PUBLIC.users;

Restore from the export:

RUNSCRIPT FROM '/path/to/full-export.sql';

Using in Java Code

When I needed to automate this in my Java application, I used JDBC:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class H2Export {
    public void exportTable(String dbPath, String user,
                           String password, String outputPath) {
        String url = "jdbc:h2:" + dbPath;

        try (Connection con = DriverManager.getConnection(url, user, password);
             Statement stmt = con.createStatement()) {

            stmt.execute("SCRIPT SIMPLE TO '" + outputPath + "' " +
                         "TABLE PUBLIC.users");
            System.out.println("Export completed: " + outputPath);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

When to Use This

This approach works well for:

Migrations: Move database between development, test, and production environments
Testing: Create reproducible database states for integration tests
Version Control: Track database schema and reference data in git
Documentation: Self-documenting database structure

What to Watch Out For

I ran into a few issues:

Large datasets: The SQL file can get very large if you have a lot of data. For huge tables, consider filtering or using other backup methods.
File paths: Make sure the output path is writable by the H2 process. On Linux, I often use /tmp for quick exports.
Schema names: Always include the schema name (like PUBLIC.users) when exporting specific tables, or the command might not find the table.
SCRIPT vs BACKUP: The SCRIPT command produces SQL statements. The BACKUP TO command creates a binary backup. They serve different purposes—use SCRIPT when you need readable SQL.

Summary

H2’s SCRIPT command exports both schema and data as SQL statements, which is perfect for migrations, test setups, and version-controlled database snapshots. Combined with RUNSCRIPT for restoration, it provides a complete logical backup-restore workflow.

The key points:

Use SCRIPT TO 'file.sql' to export the entire database
Use SCRIPT SIMPLE TO 'file.sql' TABLE tablename for specific tables
Use RUNSCRIPT FROM 'file.sql' to restore
SQL scripts preserve structure and data, unlike CSV exports

Final Words + More Resources

My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

👨‍💻 H2 Database Documentation

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!