عثمان غني عثمان غني - 24 days ago 6
SQL Question

SQL "SCRIPT" command to backup h2 database

I have an application with

h2
database. I want to create
.sql
file using
SCRIPT
command in Java.

If I am executing it using Prepared Statement:

PreparedStatement stmt = con.prepareStatement("SCRIPT");
ResultSet rs = stmt.executeQuery();


Then how can I able to get whole result in single String. I am new to Java so unable to find the way out to get result of that query because it doesn't contains column names in it.

Then I will write it in file using
InputStream
.

Answer

If you want to backup into a file, the content of your H2 instance as a SQL script, you can directly use SCRIPT TO 'path/to/my/file.sql'.

try (Connection con = ...;
     Statement stmt = conn.createStatement()) {
    stmt.executeUpdate(String.format("SCRIPT TO '%s'", sqlFilePath));
}

If you want to backup it as a ZIP archive, you can use BACKUP TO 'path/to/my/file.zip'.

try (Connection con = ...;
     Statement stmt = conn.createStatement()) {
    stmt.executeUpdate(String.format("BACKUP TO '%s'", zipFilePath));
}