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?
Add a comment
|
3 Answers
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.
5 Comments
Derek
I think this is the correct answer. You really should mark it as the correct answer.
Marki
Maybe one should also ask the question about the restore, not only the backup ;-)
VB_
@ThomasMueller why
SCRIPT TO 'filename' creates only schema-related queries, not insert queries?
Thomas Mueller
@VolodymyrBakhmatiuk by default it creates
insert statements as well, but you can disable that (see the docs).
juanmirocks
How to restore the db, also answered by @Thomas: stackoverflow.com/questions/10675768/…
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';
2 Comments
mxmlc
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.".
jnovacho
In H2 v2.3.232 you can call
SCRIPT NODATA to 'dump.txt' to get the result without data.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...]),
1 Comment
wolfgunner
It works, but you should shut down the database ! Thanks ;)