CODEWITHSUNDEEP
h2
Lbro
Lbro

Reputation: 361

H2 dump tables content only

I try to backup the tables content of a H2 DB.

I run : SCRIPT TO '/opt/data/2019-10-10_tr.sql' TABLE EVENEMENT, PASSAGE, COURSE, LIGNE but the file generated contains some information like :

SET DB_CLOSE_DELAY -1;         
;              
CREATE USER IF NOT EXISTS SA SALT '7ab09337026fac20' HASH    'c...fa387' ADMIN;            
CREATE SEQUENCE PUBLIC.HIBERNATE_SEQUENCE START WITH 5664;     
CREATE MEMORY TABLE PUBLIC.COURSE(
...

Which I don't want (that's why I wanted to dump only the tables). I don't want them because when I run RUNSCRIPT FROM '/opt/data/2019-10-10_tr.sql' I have an exception :

CREATE SEQUENCE PUBLIC.HIBERNATE_SEQUENCE START WITH 5664 [90035-197]: org.h2.jdbc.JdbcSQLException: Sequence "HIBERNATE_SEQUENCE" already exists; SQL statement:
CREATE SEQUENCE PUBLIC.HIBERNATE_SEQUENCE START WITH 5664 [90035-197]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)

And I have this exception because the database is initialized by ddl : <property name="hibernate.hbm2ddl.auto" value="create-drop" /> I don't wish to change this ; basically by saving only database content and restore it in an existing db : it should work, isn't it ? So the question is what is wrong with my SCRIPT syntax though it doesn't save the tables content only ?

Upvotes: 0

Views: 1792

Answers (1)

Evgenij Ryazanov
Evgenij Ryazanov

Reputation: 8188

SCRIPT command is not designed to export only the data, it is designed to export the schema with or without the data. Currently there is no built-in command for that purpose.

You can try to add the ˙DROP˙ clause to this command to generate commands for dropping existing tables, but you still may have a problem with sequences and your tables will be redefined, so all changes in autogenerated schema will be lost.

You can filter out all non-INSERT commands from the script with your own code.

You can export the complete script and execute DROP ALL OBJECTS before RUNSCRIPT and overwrite everything with it.

Upvotes: 2

Related Questions