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?

score 186 · Answer 1 · edited Sep 04 '19 at 18:24

186

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 04 '19 at 18:24

Pyves

6,333
7
41
59

answered Jul 15 '10 at 19:23

Thomas Mueller

48,905
14
116
132

28

I think this is the correct answer. You really should mark it as the correct answer. – Derek Sep 21 '12 at 21:14
4

Maybe one should also ask the question about the restore, not only the backup ;-) – Marki Apr 21 '16 at 09:00
1

@ThomasMueller why `SCRIPT TO 'filename'` creates only schema-related queries, not insert queries? – VB_ Dec 12 '16 at 14:49
1

@VolodymyrBakhmatiuk by default it creates `insert` statements as well, but you can disable that (see the docs). – Thomas Mueller Dec 12 '16 at 20:01
1

How to restore the db, also answered by @Thomas: https://stackoverflow.com/questions/10675768/executing-script-file-in-h2-database/10679673 – juanmirocks Jun 11 '18 at 09:49

score 12 · Answer 2 · edited Jun 30 '20 at 12:47

12

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 '20 at 12:47

Community

1
1

answered Jun 25 '20 at 06:42

ahmeticat

1,899
1
13
28

score 3 · Answer 3 · edited Jun 10 '21 at 21:41

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...]),

How in H2DB get sql dump like in MySql?

3 Answers3

Linked