Hi Bob, This info is very valuable. Thanks so much for taking the time to provide this detail -- greatly appreciated.
Best regards, Sridhar On Aug 2, 2:35 pm, bob mcgee <[email protected]> wrote: > Sridhar, > The BACKUP function is the suggested way to accomplish on-line > backups. The similar "SCRIPT TO" command is also safe (albeit much > slower) and provides a backup that is usable between different > versions of H2 and between the old and new page store. Both guarantee > that the ZIP includes all committed transactions and all necessary > files, with no potential problems. > > What I said earlier about directly zipping up the DB files is > misleading, and I apologize. > To clarify: > H2's durability and integrity features MAY make it possible to backup > a live DB by just copying the files, but IT IS STILL A BAD IDEA. The > issue is that one file may be modified after being read by your > compression utility, while another file may receive the new > modification before being archived. If there is a relation between > the files, problems can occur. This is a concurrency problem -- in > practice, problems may not occur often, but it is dangerous to rely on > it. > > For offline backups: > If you close the database and wait until the lock file is fully > cleared, than you may safely back up the files. You should make sure > to back up the externally-stored LOB files as well as the main data > file(s). I believe you need the log files too -- closing the DB > should clean them up, making them small anyway. If you choose not to > include the index file, it will be rebuilt when the DB is next > opened. This can be very time consuming for large databases -- slower > than inserting the data from scratch with no indexes. > > To explain what I said earlier: > I was backing up open DBs which were being mostly read, not written. > In practice, it should not cause integrity problems (most of the > time), but the DBMS is not obligated to ensure this. It can't, because > the problems lie with the filesystem and what version(s) of files your > backup/archival tool sees. If all the files were read & copied > atomically (as one operation, instantaneously) it would not be an > issue; some filesystems have features that do this, but they are > generally only used for on-line filesystem backups. Some archival > utilities will use these features if available, but the safest way is > still to use the built-in DBMS backup function. Externally-stored > LOBs, indices, and necessary log files are the possible problem > spots. If the main DB file is split into multiple files (due limits > on >4 GB files) this can be another problem. > > I know this is more info than you probably want or need. I figured I > should explain a bit more for others who have considered using this > technique, and may not be aware of the potential issues. > > Regards, > Bob McGee > On Aug 1, 3:16 pm, "M. A. Sridhar" <[email protected]> wrote: > > > Just a couple of quick follow-ups: > > 1. Since I want a backup operation, I don't mind losing some currently > > live data, but can I safely assume that the zip file will be in a > > usable state when restored? > > 2. h2 creates three files, .data, .index and .log. Can I safely > > assume that I only need to back up the .data file, and that the .index > > and .log files will be recreated if necessary by h2 if the backup is > > restored? > > > Thanks in advance, > > Sridhar > > > On Jul 31, 7:32 am, bob mcgee <[email protected]> wrote: > > > > Hi, > > > What about using the BACKUP function? It should do his automatically > > > and cleanly -- seehttp://www.h2database.com/html/grammar.html#backup > > > > Your suggested idea actually works too though, from personal > > > experience. I tend to zip up my larger DBs with 7Zip for compact > > > offline storage, and I've accidentally zipped a running DB a couple > > > times. Nothing says "good times" quite like a 700 MB DB compressing > > > to 100 MB or less! > > > > Remember, like any good DBMS, H2 is durable against power outage, so > > > being suddenly cut off (by writing to ZIP intermediately) cannot leave > > > the DB in an inconsistent or destructive state. > > > > You will lose the current transaction and have to rebuild the index > > > though, and may lose *some* of the data, including modifications > > > within the WRITE_DELAY time interval. Those are why I don't suggest > > > it. > > > > Cheers, > > > Bob McGee > > > > On Jul 31, 1:35 am, "M. A. Sridhar" <[email protected]> wrote: > > > > > Hello, > > > > > I use h2 in embedded mode. I'm wondering if I can simply zip up the > > > > files in its directory as a backup, while the application is running. > > > > The intent is that I can simply unzip the files to restore from the > > > > backup. The problem I'm worried about is if the embedded database's > > > > state is cached by the currently running application, in which case > > > > the zip file's contents can be inconsistent and therefore unusable. > > > > > Of course I can perform the zip operation after shutting down the > > > > application, but I want to avoid that if possible, because it is a > > > > running web app. > > > > > Thanks in advance for your advice. > > > > > Regards, > > > > Sridhar --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
