H2 Database Online Backup in an Spring, Hibernate ORM Solution with EntityManagers as persistenceUnits

[Peter wein.peter]

Hi!

I want to backup my h2.db, which is almost accessed by at max half a dozen people at one time, normally only one:

1. Approach was to copy it and just to log off the user and make manually sure nobody else would access the database. Is this a working approach, although unsafe?
2. Because I did not find first solution very attractive I was looking for another option and find the excerpt from the h2 tutorial from its home page.

So I can do an online backup (while Database is connected to my Tomcat web app):

"

Online Backup

The BACKUP SQL statement and the Backup tool both create a zip file with the database file. However, the contents of this file are not human readable.

The resulting backup is transactionally consistent, meaning the consistency and atomicity rules apply.

BACKUP TO 'backup.zip'
"

 
So my persistenceUnit is connected to 12 persistence classes, which represents the 12 tables of the database. I made my queries with

```java  [code]
    @PersistenceContext(unitName="persistenceUnit")
    EntityManager entityManager;

        public List<TableLock> findAllTableLocks() {
            return entityManager.createQuery("select o from TableLock o", TableLock.class).getResultList();
        }
```java [/code]

But this is not working for my example:

[code]
            try {
                filename = "/media/usb/backup.zip";//filename +"2";
                q =  entityManager.createNamedQuery("BACKUP TO \'"+filename+"\'", TableLock.class).executeUpdate();
                entityManager.getTransaction().commit();

            } catch (Exception e) {
                e.printStackTrace();
            }
[/code]

will throw an

InvalidDataAccessApiUsageException! Why?

Any hints appreciated!

Thanks in advance, cause I am little stuck...

Peter

[Peter wein.peter]

Solution ! Ok, I am so glad, I was able to do the backup with the following command sequence:

if (FilesOnUSB.file != null) {

     //get Session from the unwrap method of our persistenceUnit 'entityManager'
     Session session = entityManager.unwrap(Session.class);
     // Begin of a sql query n the session
     session.beginTransaction();
     // create data consistent call of backup command of h2 and execute it directly
     session.createSQLQuery("BACKUP TO \'/media/usb/erestau.zip\'").executeUpdate();
     // finalize sql query, flush session
     session.getTransaction().commit();

session = null;

}

Now the database was saved, even though it seems a perfect copy of what I get by my plane file copy. But this is the guaranteed way of a data consistent database at that time!

This is my final question: Is this now fail safe copy which can in every case reproduce the state of the database at that time. What are the ways to enforce this?