How in H2Db Get SQL Dump Like in MySQL

H2 dump tables content only

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.

H2 database dump

When you shutdown your application and restart it.

Your script is executed again and it tries to create a new table hence throwing table exists error.

Therefore you need to specifiy what happens when your application is restarted.

spring.jpa.hibernate.ddl-auto=update

if you need to update your table

to delete and recreate table use

spring.jpa.hibernate.ddl-auto=create-drop

You can read more on the following links

How hibernate works

how hibernate works baeldung

In H2 db something like query log available like in mySQL?

For the H2 database, if you append ;TRACE_LEVEL_FILE=2 to the database URL, then the SQL statements that are executed are written to the file {databaseName}.trace.db. There is also a tool to analyze this trace file.

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

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

---

Related Topics