CODEWITHSUNDEEP
sqlh2dump
palych063
palych063

Reputation: 1139

How in H2DB get sql dump like in MySql?

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?

Upvotes: 79

Views: 66751

Answers (3)

debuglevel
debuglevel

Reputation: 508

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

Upvotes: 7

ahmeticat
ahmeticat

Reputation: 1939

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';

Upvotes: 15

Thomas Mueller
Thomas Mueller

Reputation: 50147

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.

Upvotes: 193

Related Questions