H2 Database: How to restore from a backup.

H2 is a database, like Oracle, MS SQL server, Teradata etc and like them should be regularly backed up.
Restoring from a backup copy is both simple and more than likely to be successful. These steps were created by members of our Professional Services team, Stony Smith and Ernest Jones.

There are a couple of options for dealing with a corrupt H2 Database. 

OPTION 1: Install an empty one

There is a solution for recovering from a corrupt H2 database within Logistics Manager by installing a
new (empty) one. Please note that your LXA's and schedules will be lost. 

Installation Steps:

 -stop the H2 service

- Stop the Jetty service

- Stop the LAE service

- Go to <installdir>/data and find the file automationDb.h2

- Rename this file to automationDb.h2.old (and/or make a local copy off somewhere else)

- Ensure that any *.h2.lock or *h2.trace file is deleted from the data folder

- Copy the backup automationDb.h2 file into the folder

- Start the H2 service

- Start the Jetty service

- Start the LAE service

OPTION 2:  Recover the old one

These instructions are very similar to the above but are the steps required to recover a corrupt but already populated database, thus recovering the existing scheduling information.

Note: While these steps work they have not always been successful even with some manual changes to address problems with particular records. Perform that at your own risk.

Recovery Steps:

(This assumes you have a Java installation in your path)
(The exact name of the h2 jar may change depending on the version)

1. Stop the H2 DB and the jetty service
2. Back up the DB file in <Logisics_mamanger_root>/data (usually called automatioDb.h2.db)
3. Create a recovery file
a. cd to <Logisics_mamanger_root>/data
b. from that location execute:

java –cp <Logisics_mamanger_root>/h2/bin/h2-1.3.174.jar org.h2.tools.Recover

4. This should produce a file called automationDb.h2.sql with all the recovered H2 DB data
5. Now delete the corrupted (and previously backed up) automationDb.h2.db file (make sure to keep .sql file)
6. Start the DB service
7. Open the following file : <Logisics_mamanger_root>/jetty/resource/lavastorm_datasource.xml and look for the <Set name='jdbcUrl'> tag

8. Copy the jdbcUrl (everything in that tag except for the MVCC=true part. Should look like this:

jdbc:h2:tcp://localhost:9092/<Logisics_mamanger_root>/data/automationDb

9. If you don’t have H2 Console installed, install it
10. Start the H2 Console and connect to the jdbcUrl specified. Make sure the user is called laeAutomationUser
and that the password is lavastorm (the console is accessed via a browser and is usually running on port 8082)
11. After clicking connect a brand new empty DB should be created.
12. Now disconnect the console
13. Recreate the DB from the recovered file
a. From the data folder run:

java -cp <Logisics_mamanger_root>/h2/bin/h2-1.3.174.jar org.h2.tools.RunScript
-url " jdbc:h2:tcp://localhost:9092/<Logisics_mamanger_root>/data/automationDb" -user laeAutomationUser -
password lavastorm -script automationDb.h2.sql

Now you can start the jetty service and you should be able to log into Logistics Manager.

Depending on what sort of data loss the DB has actually suffered, it may be necessary to fix some table entries manually.