Derby
  1. Derby
  2. DERBY-5508

Improve backup/restore documentation visibility and content to encourage proper backups and restore procedures

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.8.2.2, 10.9.1.0
    • Fix Version/s: 10.8.3.0, 10.9.1.0
    • Component/s: Documentation
    • Labels:
      None
    • Issue & fix info:
      High Value Fix

      Description

      In a very large percentage of cases where users need to restore their backup, I have found that they don't have a usable backup to restore. It is often difficult to determine the root cause, so I am not sure of all of these, but my experience and suspicions are:
      1) They relied on a operating system backup while the database was not shutdown or frozen.
      2) They unknowingly backed up a corrupt database over their good backup because there was corruption that was not readily apparent at the time of the backup.
      3) They relied on incremental backups (even when the database was shutdown) that did not properly track deletes.
      4) They restored to an existing database directory without removing the old one first.
      5) They didn't backup because the Derby backup was not included in the embedding application''s backup procedure.
      6) The embedding application's did have a backup/restore procedure but it made one of the mistakes above.

      It would be good to think about our backup and restore documentation to make it more visible to users and developers and include tips for good backup procedures. Two things I can think of that might help are.
      1) Encourage SYSCS_UTIL.SYSCS_CHECK_TABLE on all the tables of the database before backing up to help flag existing corruption before potential existing issues before overwriting a backup.
      2) Put pointers in the Getting Started Guide and the Developers guide to the backup instructions in the Administration manual.

      I am sure others have ideas on how to improve the doc. Kim suggested I log this issue and track the various ideas so she can document them for the next release.

      The relevant doc references are:
      http://db.apache.org/derby/docs/10.8/adminguide/cadminbckupdb.html
      http://db.apache.org/derby/docs/10.8/adminguide/radminconsist24642.html

      1. DERBY-5508-2.zip
        41 kB
        Kim Haase
      2. DERBY-5508-2.diff
        26 kB
        Kim Haase
      3. DERBY-5508.zip
        40 kB
        Kim Haase
      4. DERBY-5508.stat
        0.8 kB
        Kim Haase
      5. DERBY-5508.diff
        26 kB
        Kim Haase

        Activity

        Hide
        Kim Haase added a comment -

        Changes have appeared in Latest Alpha Manuals.

        Show
        Kim Haase added a comment - Changes have appeared in Latest Alpha Manuals.
        Hide
        Kim Haase added a comment -

        Thanks, Rick.

        Committed patch DERBY-5508-2.diff to documentation trunk at revision 1341869.
        Merged to 10.8 doc branch at revision 1341882.

        Show
        Kim Haase added a comment - Thanks, Rick. Committed patch DERBY-5508 -2.diff to documentation trunk at revision 1341869. Merged to 10.8 doc branch at revision 1341882.
        Hide
        Rick Hillegas added a comment -

        Thanks, Kim. The second patch looks good. +1

        Show
        Rick Hillegas added a comment - Thanks, Kim. The second patch looks good. +1
        Hide
        Kim Haase added a comment -

        Thanks for that advice, Rick. I'm attaching a second patch, DERBY-5508-2.diff and DERBY-5508-2.zip, with additional changes to the following topics:

        src/ref/rrefsyscschecktablefunc.dita
        src/adminguide/cadminconsist01.dita
        src/adminguide/cadminbckupdb.dita
        src/adminguide/cadminhubbkup12677.dita

        The topic src/adminguide/cadmindbintegrity.dita also needs a change, but I will include that in the fix to the just-reopened DERBY-5691.

        Show
        Kim Haase added a comment - Thanks for that advice, Rick. I'm attaching a second patch, DERBY-5508 -2.diff and DERBY-5508 -2.zip, with additional changes to the following topics: src/ref/rrefsyscschecktablefunc.dita src/adminguide/cadminconsist01.dita src/adminguide/cadminbckupdb.dita src/adminguide/cadminhubbkup12677.dita The topic src/adminguide/cadmindbintegrity.dita also needs a change, but I will include that in the fix to the just-reopened DERBY-5691 .
        Hide
        Rick Hillegas added a comment -

        Thanks for this patch, Kim. I agree that it's a good idea to mention all four backup procedures in "Using the backup procedures to perform an online backup", so +1 to that change.

        I am less certain about the advice that applications should run SYSCS_UTIL.SYSCS_CHECK_TABLE on every table in the database before a backup. That would be an expensive operation. A commit-then-review approach makes more sense to me: first backup the database and then in a separate, offline process run SYSCS_UTIL.SYSCS_CHECK_TABLE on the backup to verify its integrity. Don't throw away older backups until you have verified the new one.

        Thanks,
        -Rick

        Show
        Rick Hillegas added a comment - Thanks for this patch, Kim. I agree that it's a good idea to mention all four backup procedures in "Using the backup procedures to perform an online backup", so +1 to that change. I am less certain about the advice that applications should run SYSCS_UTIL.SYSCS_CHECK_TABLE on every table in the database before a backup. That would be an expensive operation. A commit-then-review approach makes more sense to me: first backup the database and then in a separate, offline process run SYSCS_UTIL.SYSCS_CHECK_TABLE on the backup to verify its integrity. Don't throw away older backups until you have verified the new one. Thanks, -Rick
        Hide
        Kim Haase added a comment -

        Attaching DERBY-5508.diff, DERBY-5508.stat, and DERBY-5508.zip, with changes to 18 files:

        M src/devguide/tdevcsecurenewbootpw.dita
        M src/devguide/tdevcsecurenewextkey.dita
        M src/devguide/tdevcsecureunencrypteddb.dita
        M src/getstart/cwwdsummary.dita
        M src/getstart/cgsquck19524.dita
        M src/adminguide/cadminbckupdb.dita
        M src/adminguide/cadminconsist01.dita
        M src/adminguide/cadminhubbkup01.dita
        M src/adminguide/cadminhubbkup12677.dita
        M src/adminguide/cadminhubbkup63476.dita
        M src/adminguide/cadminhubbkup67525.dita
        M src/adminguide/cadminhubbkup98797.dita
        M src/ref/rrefbackupdbenablelogproc.dita
        M src/ref/rrefbackupdbproc.dita
        M src/ref/rrefdisablelogproc.dita
        M src/ref/rreffreezedbproc.dita
        M src/ref/rrefsyscschecktablefunc.dita
        M src/ref/rrefunfreezedbproc.dita

        I hope I have provided some overviews and links in the Admin Guide that guide users in the right directions.

        One of the Developer's Guide topics I mentioned earlier turned out to be more about authorization than backups, so I added Admin Guide cross-references to only 3 topics in that book.

        The changes to the "What next with Derby?" topic in Getting Started really have nothing to do with the backup issues except that the topic now points to the Derby documentation. It was just a good chance to clean it up.

        I also took advantage of the opportunity to correct a typo and update some very old dates in some reference topics.

        More suggestions are welcome. Thanks!

        Show
        Kim Haase added a comment - Attaching DERBY-5508 .diff, DERBY-5508 .stat, and DERBY-5508 .zip, with changes to 18 files: M src/devguide/tdevcsecurenewbootpw.dita M src/devguide/tdevcsecurenewextkey.dita M src/devguide/tdevcsecureunencrypteddb.dita M src/getstart/cwwdsummary.dita M src/getstart/cgsquck19524.dita M src/adminguide/cadminbckupdb.dita M src/adminguide/cadminconsist01.dita M src/adminguide/cadminhubbkup01.dita M src/adminguide/cadminhubbkup12677.dita M src/adminguide/cadminhubbkup63476.dita M src/adminguide/cadminhubbkup67525.dita M src/adminguide/cadminhubbkup98797.dita M src/ref/rrefbackupdbenablelogproc.dita M src/ref/rrefbackupdbproc.dita M src/ref/rrefdisablelogproc.dita M src/ref/rreffreezedbproc.dita M src/ref/rrefsyscschecktablefunc.dita M src/ref/rrefunfreezedbproc.dita I hope I have provided some overviews and links in the Admin Guide that guide users in the right directions. One of the Developer's Guide topics I mentioned earlier turned out to be more about authorization than backups, so I added Admin Guide cross-references to only 3 topics in that book. The changes to the "What next with Derby?" topic in Getting Started really have nothing to do with the backup issues except that the topic now points to the Derby documentation. It was just a good chance to clean it up. I also took advantage of the opportunity to correct a typo and update some very old dates in some reference topics. More suggestions are welcome. Thanks!
        Hide
        Kim Haase added a comment -

        I'm somewhat confused about another of the items in the list of backup/restore problems: Item 3 mentions incremental backups. How do you do an incremental backup in Derby? There is no indication that any of the Derby backup procedures does an incremental backup, let alone one that would "properly track deletes."

        Thanks for any help.

        Show
        Kim Haase added a comment - I'm somewhat confused about another of the items in the list of backup/restore problems: Item 3 mentions incremental backups. How do you do an incremental backup in Derby? There is no indication that any of the Derby backup procedures does an incremental backup, let alone one that would "properly track deletes." Thanks for any help.
        Hide
        Kim Haase added a comment -

        Here are some initial thoughts about what work is needed for this issue. Comments are welcome – there are several items I'm particularly unsure about. In the meantime, I'll get to work.

        The topics that need work in order to help people back up databases properly include the following (there may be more).

        Admin Guide:
        The material here is focused on the tasks alone. The introductory topic, "Backing up and restoring databases" (cadminhubbkup98797.dita), should be enhanced with enough conceptual information to help head off the problems people might encounter. The two online backup topics should insert the instruction to run SYSCS_UTIL.SYSCS_CHECK_TABLE before backing up. Similarly, the "Checking database consistency" topic (cadminconsist01.dita) should mention that the function should be run before backups.

        It seems odd that the backup procedure needed to make roll-forward recovery possible is not actually mentioned until the "Roll-forward recovery" topic (cadminrollforward.dita). In fact, the topic "Using the backup procedure to perform an online backup" (cadminhubbkup01.dita) mentions only one of the four backup system procedures. Seems as if it should describe all four and when each should be used. I'm not sure under what circumstances the NOWAIT versions should be used, actually – the Reference Manual topics don't say.

        Reference Manual:
        The "SYSCS_UTIL.SYSCS_CHECK_TABLE system function" topic should include a mention that it should be run before backups, and a reference to the Admin Guide should be added.
        All the material on the SYSCS_UTIL.SYSCS_BACKUP_DATABASE procedures, the SYSCS_UTIL.SYSCS_FREEZE_DATABASE and SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE procedures should cross-reference the Admin Guide.
        The "restoreFrom=path attribute" topic (rrefrestorefrom.dita) already refers to the Admin Guide. However, it implies that it is not possible to overwrite a database when you do a restore (as in item 4 in your problem list), at least if you use this attribute. Do the problems result from operating system restores rather than those that use this attribute? This may also affect the other attribute topics: "createFrom=path attribute" (rrefcreatefrom.dita), "rollForwardRecoveryFrom=path attribute" (rrefrollforward.dita).

        Getting Started:
        There's no obvious place to insert specific pointers about backing up databases, since the information in this guide is generally more basic than that.
        The "Product documentation for Derby" topic already mentions backups among the Admin Guide tasks.
        The "What next with Derby?" topic at the end of the tutorial could use some major rearrangement and rewriting, actually. The Wiki page it most prominently points to seems seriously out of date and should probably not be referenced, at least not prominently – instead the material at the end (paragraph and bullet list) should be moved up to replace it. Also, the first place pointed to should be the rest of the Derby documentation – maybe a pointer back to the "Product documentation for Derby" topic.
        The "Quick start guide for experienced JDBC users" topic should also add a paragraph pointing to the product documentation topic.

        Developer's Guide:
        There are existing pointers in "Preparing to upgrade" (tdevpreupgrade.dita), "Creating, dropping, and backing up databases" (cdevdvlp42173.dita), and "Using in-memory databases" (cdevdvlpinmemdb.dita).
        The following topics could use a pointer to the Admin Guide where backups are mentioned: "Upgrading an old database to use SQL standard authorization" (cdevcsecuresqlauthupgrade.dita), "Encrypting an existing unencrypted database" (tdevcsecureunencrypteddb.dita), "Encrypting databases with a new external encryption key" (tdevcsecurenewextkey.dita), "Encrypting databases with a new boot password" (tdevcsecurenewbootpw.dita).

        Show
        Kim Haase added a comment - Here are some initial thoughts about what work is needed for this issue. Comments are welcome – there are several items I'm particularly unsure about. In the meantime, I'll get to work. The topics that need work in order to help people back up databases properly include the following (there may be more). Admin Guide: The material here is focused on the tasks alone. The introductory topic, "Backing up and restoring databases" (cadminhubbkup98797.dita), should be enhanced with enough conceptual information to help head off the problems people might encounter. The two online backup topics should insert the instruction to run SYSCS_UTIL.SYSCS_CHECK_TABLE before backing up. Similarly, the "Checking database consistency" topic (cadminconsist01.dita) should mention that the function should be run before backups. It seems odd that the backup procedure needed to make roll-forward recovery possible is not actually mentioned until the "Roll-forward recovery" topic (cadminrollforward.dita). In fact, the topic "Using the backup procedure to perform an online backup" (cadminhubbkup01.dita) mentions only one of the four backup system procedures. Seems as if it should describe all four and when each should be used. I'm not sure under what circumstances the NOWAIT versions should be used, actually – the Reference Manual topics don't say. Reference Manual: The "SYSCS_UTIL.SYSCS_CHECK_TABLE system function" topic should include a mention that it should be run before backups, and a reference to the Admin Guide should be added. All the material on the SYSCS_UTIL.SYSCS_BACKUP_DATABASE procedures, the SYSCS_UTIL.SYSCS_FREEZE_DATABASE and SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE procedures should cross-reference the Admin Guide. The "restoreFrom=path attribute" topic (rrefrestorefrom.dita) already refers to the Admin Guide. However, it implies that it is not possible to overwrite a database when you do a restore (as in item 4 in your problem list), at least if you use this attribute. Do the problems result from operating system restores rather than those that use this attribute? This may also affect the other attribute topics: "createFrom=path attribute" (rrefcreatefrom.dita), "rollForwardRecoveryFrom=path attribute" (rrefrollforward.dita). Getting Started: There's no obvious place to insert specific pointers about backing up databases, since the information in this guide is generally more basic than that. The "Product documentation for Derby" topic already mentions backups among the Admin Guide tasks. The "What next with Derby?" topic at the end of the tutorial could use some major rearrangement and rewriting, actually. The Wiki page it most prominently points to seems seriously out of date and should probably not be referenced, at least not prominently – instead the material at the end (paragraph and bullet list) should be moved up to replace it. Also, the first place pointed to should be the rest of the Derby documentation – maybe a pointer back to the "Product documentation for Derby" topic. The "Quick start guide for experienced JDBC users" topic should also add a paragraph pointing to the product documentation topic. Developer's Guide: There are existing pointers in "Preparing to upgrade" (tdevpreupgrade.dita), "Creating, dropping, and backing up databases" (cdevdvlp42173.dita), and "Using in-memory databases" (cdevdvlpinmemdb.dita). The following topics could use a pointer to the Admin Guide where backups are mentioned: "Upgrading an old database to use SQL standard authorization" (cdevcsecuresqlauthupgrade.dita), "Encrypting an existing unencrypted database" (tdevcsecureunencrypteddb.dita), "Encrypting databases with a new external encryption key" (tdevcsecurenewextkey.dita), "Encrypting databases with a new boot password" (tdevcsecurenewbootpw.dita).
        Hide
        Kim Haase added a comment -

        Thank you very much for filing this issue, Kathey. I'll work on this for 10.9.

        Show
        Kim Haase added a comment - Thank you very much for filing this issue, Kathey. I'll work on this for 10.9.

          People

          • Assignee:
            Kim Haase
            Reporter:
            Kathey Marsden
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development