Reputation: 1139
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
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
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
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