CODEWITHSUNDEEP
javah2database-backups
Alexander Kondaurov
Alexander Kondaurov

Reputation: 4123

Create h2 backup without alter statements

There's H2's specific statement Script, i'm using it to create sql file without any data:

script NODATA to 'db.sql'

But i can't find an option to say that i don't want alter statements. I would name this option like 'ONLYDATA'. I want to create sql file without statements like 'create table, create sequence, alter table...' but only with insert statements.

I want this because i want to load data on existing database, that already has some data, and sql script with create statements fails on it

Upvotes: 0

Views: 283

Answers (1)

Evgenij Ryazanov
Evgenij Ryazanov

Reputation: 8178

H2 has BACKUP TO 'filename.zip' command for online backups. SCRIPT can be used for this purpose too (and should be used if you need to move your data to another version of H2), but it's not a backup command, it's a data export command.

If you want to export SQL script into existing non-empty database you have two alternatives.

  1. Execute DROP ALL OBJECTS in target database to make it empty before execution of RUNSCRIPT FROM. This way is reliable.
  2. Use SCRIPT DROP … when you create this script. It will generate additional DROP commands for your tables. You can also use SCHEMA schemaName or TABLE tableName clause if you want to export only some tables. You may run into some other issue, however, with sequences or something else.

If you need to merge existing data with data from the script, you can't do it in that way. You need to import it in another database and create linked tables from it (or into existing database under another schema) and run MERGE INTO tableName USING scriptTableName ON … commands for each table with properly written matching rules and actions, for example.

Upvotes: 1

Related Questions