After using multiple versions of HSQLDB (1.7.1, 1.7.2, 184.108.40.206), I don't think I've ever seen recovery from a ".backup" file work after a crash. I've also experimented with deleting the ".data" file or making it zero length (while the database is not running). In both cases, attempts to open the database (in-process with cached tables) fail due to errors in the script file (typically an IndexOutOfBoundsException while executing the script file some commands involving indices.).
My understanding from reading the documentation and posts on this forum and the Help forum is that recovery is supposed to occur automatically whenever corruption is detected in the ".data" file while opening the database. If this is true, it must be a very limited form of "corruption" detection since the tests I described above fail. (Details: Tests have mostly been performed under Java 1.4.2 on Linux.)
As a consequence of the above and for additional robustness reasons, in my application I've implemented an independent database recovery mechanism that lies entirely outside HSQLDB. I need this mechanism anyway, but it lessens the value of deploying replicated databases which include the ".backup" file.
Can someone please clarify how the recovery mechanism actually works, and whether there are any plans to improve it?
This process is very clearly explained in the Guilde, Appendix C. Hsqldb Database Files and Recovery.
Recovery can mean different things in different contexts. In this context, it simply means that if the .data file was not fully written at the end of a session (because the SHUTDOWN or SHUTDOWN COMPACT command was not used to close the database), the .backup file is inflated to restore the .data file to its state at the time of last CHECKPOINT or proper SHUTDOWN. After this the old .log is replayed.
Instead of tampering with the .data file, change the modified=no property of the .properties file to modified=no and see what happens.
You are absolutely right to use extra backup and recovery procedures to suit you deployment scenario. Note that the two files left at the end of a SHUTDOWN SCRIPT can be used for backup purposes in a relatively database version-independent manner.
change the modified=no property of the .properties file to modified=yes and see what happens.
Yes, changing to "modified=yes" in the .properties files worked as advertised to restore from the ".backup" in my deleted/zero-length ".data" file test scenarios. I had missed that little detail in the documentation (it probably should be put in the "Repair" section of Appendix C).