How in H2DB get sql dump like in MySql?

Question

I have H2DB database which stores data in files. I have 3 files: test.18.log.db, test.data.db, and test.index.db.

I want get SQL dump file like when I use mysqldump. Is it possible?

Pyves · Accepted Answer · 2019-09-04 18:24:05Z

193

Yes, there are multiple solutions. One is to run the SCRIPT SQL statement:

SCRIPT TO 'fileName'

Another is to use the Script tool:

java org.h2.tools.Script -url <url> -user <user> -password <password>

Then, there are also the RUNSCRIPT statement and RunScript tool.

By the way, you should consider upgrading to a more recent version of H2. With newer versions, the two files .data.db and .index.db are combined in to a .h2.db file.

edited Sep 4, 2019 at 18:24

Pyves

6,5338 gold badges44 silver badges61 bronze badges

answered Jul 15, 2010 at 19:23

Thomas Mueller

50.4k16 gold badges123 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Derek Over a year ago

I think this is the correct answer. You really should mark it as the correct answer.

Marki Over a year ago

Maybe one should also ask the question about the restore, not only the backup ;-)

VB_ Over a year ago

@ThomasMueller why SCRIPT TO 'filename' creates only schema-related queries, not insert queries?

Thomas Mueller Over a year ago

@VolodymyrBakhmatiuk by default it creates insert statements as well, but you can disable that (see the docs).

juanmirocks Over a year ago

How to restore the db, also answered by @Thomas: stackoverflow.com/questions/10675768/…

Community · Accepted Answer · 2020-06-30 12:47:21Z

15

If you want to get schema and data, you can use

SCRIPT TO 'dump.sql';

If you want to get only schema, you can use

SCRIPT SIMPLE TO 'dump.txt';

edited Jun 30, 2020 at 12:47

CommunityBot

11 silver badge

answered Jun 25, 2020 at 6:42

ahmeticat

1,9391 gold badge16 silver badges29 bronze badges

1 Comment

mxmlc Over a year ago

I have used SCRIPT SIMPLE and it also export data. As per documentation (h2database.com/html/commands.html#script), the difference is that "SIMPLE does not use multi-row insert statements.".

StackzOfZtuff · Accepted Answer · 2021-06-10 21:41:50Z

Your shortcut:

$ ls foo.mv.db $ wget -O h2.jar https://search.maven.org/remotecontent?filepath=com/h2database/h2/1.4.200/h2-1.4.200.jar $ ls foo.mv.db h2.jar $ java -cp h2.jar org.h2.tools.Script -url "jdbc:h2:file:./foo" -user sa -password "" $ ls backup.sql foo.mv.db h2.jar $ cat backup.sql | head -n 20 ; CREATE USER IF NOT EXISTS "SA" SALT 'bbe17...redacted...' HASH 'a24b84f1fe898...redacted...' ADMIN; CREATE SEQUENCE "PUBLIC"."HIBERNATE_SEQUENCE" START WITH 145; CREATE CACHED TABLE "PUBLIC"."...redacted..."( "ID" INTEGER NOT NULL SELECTIVITY 100, [...redacted...] "...redacted..." VARCHAR(255) SELECTIVITY 100 ); ALTER TABLE "PUBLIC"."...redacted..." ADD CONSTRAINT "PUBLIC"."CONSTRAINT_8" PRIMARY KEY("ID"); -- 102 +/- SELECT COUNT(*) FROM PUBLIC.[...redacted...]; INSERT INTO "PUBLIC"."...redacted..." VALUES ([...redacted...]),

Collectives™ on Stack Overflow

How in H2DB get sql dump like in MySql?

3 Answers 3

5 Comments

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

1 Comment

Linked

Related