As far as the difference in space concerns Myrna, I believe that is possibly because the backup database is in effect being compresses while being copied. No sense copying the wasted space that results from deleted records that cannot be reclaimed by the O/S until something like SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure is called.
From: Myrna van Lunteren [mailto:[email protected]] Sent: Tuesday, October 29, 2013 11:12 PM To: derbydev Subject: Re: some questions regarding backups... From: Myrna van Lunteren [mailto:[email protected]<mailto:[email protected]>] Sent: Monday, October 28, 2013 1:26 PM To: derbydev Subject: some questions regarding backups... Hi, I have some questions regarding backups, and I hope the community can help answer these... 1. Can a backup of a database be used as a database? The directory structure of a backed up database looks very similar to a normal database. 1.b. What's the difference, if any? I think though that actually using a backed up database as if it is a database destroys it as a back-up, that is, once you start using it, even just for selects, the most recent transaction log will conflict with the ones in the original database. 1.c. Is this interpretation correct? 1.d. What would the expected behavior be if someone would use such a compromised backup to restore? 2. If no other activity has taken place, should the file sizes for the .dat files in the seg0 be the same in a backed up database as the database from which it was taken? Thanks, Myrna On Mon, Oct 28, 2013 at 1:54 PM, Bergquist, Brett <[email protected]<mailto:[email protected]>> wrote: I have always assumed that a backup create by "SYSCS_UTIL.SYSCS_BACKUP_DATABASE" system procedure creates a fully functional backup copy of the database. I have used it as such with no issues. I have replaced the original database with the backup and the continued. Not that I did not try processing any transaction logs to get it up to current state. I simply shutdown the system, deleted the current database, copied the backup database in place, and restarted the system. I did not use the "restoreFrom=path" or the "createFrom=path" connection options. I guess in doing so, I lose the system's "copying the log file to the same location they were when the backup was taken", but then again, I have left the log files in their default location. >From the manual, it states for the SYSCS_UTIL.SYSCS_BACKUP_DATABASE: The procedure then copies the entire original database directory (including data files, online transaction log files, and jar files) to the specified backup directory. And for the restoring using "restoreFrom" connection attribute: If a database with the same name exists in the derby.system.home location, the system will delete the database, copy it from the backup location, and then restart it. So it seems to me that the restore is simply a copy of the files back and restart the database. From this, I believe the backup database is a fully functioning database. I would wish that better examples for actually using the log archive mode and rolling forward recovery. I don't really understand the whole roll-forward recovery mechanism especially of what to do with the online archived logs. I can't figure out what to do with the archived log files and when they can be removed. For a system that is going to be pretty much limited touch (or as close to zero as possible) and having something like a backup cron job fire off to trigger a backup, I cannot determine from the manuals what the proper process would be to use roll-forward recovery. Thanks for your response Brett... I did a simple ls -alR seg0 on a database (after a compress) before backup, and compared that with the backed up version, and the sizes were *not* exactly the same for all files (some were the same). I looped a select count(*) through all the tables, but the counts were the same. So I wondered what could cause that difference between file size in the original and backed up database... I had the same confusion as Brett about the rollForwardRecovery; where are your transaction logfiles supposed to be? It seems from reading the manual and this document: http://www.ibm.com/developerworks/data/library/techarticle/dm-0502thalamati/ that you can do the rollForwardRecovery in the same place, with the same database name, basically overwriting what you have, something like: connect 'jdbc:derby:db1;rollForwardRecoveryFrom=/backup/db1'; But this to me seems to open up the chance of there being a mix of log files, and it's not clear to me whether you can leave the seg0/*.dat files in place or are supposed to remove them. Normally I'd expect to not have to delete files underneath the derby database directory. I suspect it's better to move/rename the old database directory (after shutting derby down) and then point the logDevice attribute to it, something like; mkdir /broken mv db1 /broken java org.apche.derby.tools.ij ij> connect 'jdbc:derby:db1;rollForwardRecoveryFrom=/backup/db1;logDevice=/broken/db1' Myrna
