Grails H2 Database DbConsole - Database backup

Question

I have grails 2.0 which comes with H2 database and dbconsole.

I want to take the database backup from dbconsole:

databse url : "jdbc:mysql://localhost/opal"

Username : root

password: (none)

in the tools section of dbconsole there is a option to backup the database.

it will ask 3 things

Target file name: ~/backup.zip(by default)

Source directory:

Source database name: opal (name of my database)

when i press run , it gives error,

   No database files have been found in directory E:/Workspace/opal for the database opal

can anybody suggest how to take the database backup.

The H2 backup tool can only back up H2 database files (not MySQL databases). You didn't set the source directory, so the tool will try to search H2 databases in the current working directory, which is E:/Workspace/opal in your case, and no H2 databases were found there. — Thomas Mueller

Rick Mangi Rick Mangi · Accepted Answer · 2012-10-09T14:10:46

I've never gotten that to work. If you just want a snapshot of data for development (load on startup) I found that using DBUnit to export/import the data worked great for me. I wrote a script to export it that I call from the console:

    class DataExport {

    def ctx = SCH.servletContext.getAttribute(GA.APPLICATION_CONTEXT)   

    def exportData() {
        println "-->export"
        def ds = ctx.dataSourceUnproxied
        println ds.dump()

        Connection jdbcConnection = ctx.dataSourceUnproxied.getConnection()
        IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
        println connection.dump()
        ITableFilter filter = new DatabaseSequenceFilter(connection);
        IDataSet dataset = new FilteredDataSet(filter, connection.createDataSet());
        FlatXmlDataSet.write(dataset, new File("full.xml").newWriter());
        connection.close()
    }
}

And then in bootstrap you can load it back in

Connection jdbcConnection
FlatXmlDataSet dataSet = new FlatXmlDataSetBuilder().build(new ClassPathResource('resources/data/full.xml').inputStream)

jdbcConnection = ctx.dataSource.getConnection()
IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
try {
     DatabaseOperation.INSERT.execute(connection, dataSet)
} catch(e) {
    e.printStackTrace()
    throw(e)
} finally {
    jdbcConnection.close()
}
log.info 'data loaded'

Grails H2 Database DbConsole - Database backup

2 Answers