How to back up the embedded H2 database engine while it is running?

Question

I would like to build up an web application with H2 database engine. However, I still don't know how to back up the data while the database is running after reading this tutorial:

http://www.h2database.com/html/tutorial.html#upgrade_backup_restore

Does H2 output its stored file to somewhere in the file system? Can I just back up the outputted files?

Moonier

Possible duplicate of How in H2DB get sql dump like in MySql? – Anodyne

Answer 1

H2 is stored on the file system, but it would be better to use the backup tools that you reference, because the file format can change between versions of H2. If you upgrade H2, it may not any longer be able to read the files it created in a previous version. Also, if you copy the files it uses, I would recommend shutting the database down first, otherwise the copied files may be unreadable by H2.

The location of the file depends on the jdbc url you specify. See the FAQ: http://www.h2database.com/html/faq.html

Buddleia

Answer 2

As per the tutorial you linked, it is not recommended to backup the database by copying the files while it is running. Here is the right way to backup the database while it is running (Scala code, but can be easily converted to Java) (Source):

val connection:java.sql.Connection = ??? // get a database connection 
connection.prepareStatement("BACKUP TO 'myFile.zip'").executeUpdate 

Cusped

Answer 3

Thx to Jus12 for the nice answer. I adapted it for JPARepositories in Spring Data and would like to share it here as I couldn't find a similar answer online:

@Modifying
@Transactional
@Query(value = "BACKUP TO ?1", nativeQuery = true)
int backupDB(String path);

Maomaoism

Answer 4

try {
    Class.forName("org.h2.Driver");
    Connection con = DriverManager.getConnection("jdbc:h2:"+"./Dbfolder/dbname", "username", "password" );
    Statement stmt = con.createStatement();
    con.prepareStatement("BACKUP TO 'backup.zip'").executeUpdate();

} catch(Exception ex) {
    JOptionPane.showMessageDialog(null, ex.toString());
}

Photocell

What reason in a string Statement stmt = con.createStatement();? – Leola