4 Replies Latest reply on Feb 10, 2011 9:43 AM by philmodjunk

    Recurent corruption of a huge database

    SergeBerney-KinSA

      Title

      Recurent corruption of a huge database

      Post

      Hello world,

      We have a client with a huge database :

      -          1.3 GB size

      -          79'892 records

      -          4'053 fields

      -          652 layouts

      -          1'743 scripts

      Since this database was once corrupted (we suppose a network shortage during database structure manipulation), this file being corrupted once or twice per day... During data entering, searching, script or structure changing (but nothing we can reproduce each time).

      We try to save as compacted, save a clone and reimport all data, but nothing is the solution : Each day, this database go to corrupt status and close. We need to restore from backup or to open it, save as compacted and this is working... for few hours...

      We check every layouts (passing one by one to check if there was one in error) : Nothing

      FileMaker infrasturcture is :

      Serveur : FileMaker 7.0v1

      Workstation : 4x FileMake 7.0v3 and 1x FileMaker 11.0v2 advanced

      FMS configuration :

        Backup script : every 2h

        Cache : 150Mb / Clear every 2 minutes

      No plugin used on this database

      Thanks in advance for any solution/suggestion !!!

      Serge Berney

      Kin SA

        • 1. Re: Recurent corruption of a huge database
          philmodjunk

          What happens when you use FileMaker 11 to recover a copy of this file? (You might try recovering a clone of the file to save time here.)

          My sincere sympathies here, but if you don't have a back up copy predating the damage to your file, you may have no choice but to rebuild your database from a new blank file and then import your data into this new file. If you have a back up copy, you can save a copy as a clone and import all your data into this clone file. Either way, the import will take a very long time to do. You may want to dedicate a computer to this and let it run for several days, then do a smaller supplementary import to add/update records added/changed since the import was initiated. (Depends on your database design on whether this supplementary import will work or not.)

          When rebuilding your file, you need not do everything from scratch. You can take a clone of your database file and import tables and scripts from it. You can copy and paste all objects from a layout to a new layout. If you do this, however, there's a chance you will import the corruption with the file. If the recover process is successfully reporting issues with your original damaged file, you can make frequent recovers of your new file while you rebuild, reverting back to a previous version if the recover finds an issue with your file.

          If you have to go this route, there's a tool called FMMigrator you may want to investigate as a way to save a lot of time and effort here. There's also a third party app called FMDiff that's worth a look as an additional tool for checking for file corruption. (And by all means, use advanced's database design report to check for disconnected objects during the rebuild by searching the report for instances of "missing" and "unknown".)

          Bottom line: always make frequent back ups. Always save lot's of back up copies. You may not discover an issue with your file (either file damage or missing/incorrect data due to user error) until a long time after it was backed up.

          • 3. Re: Recurent corruption of a huge database
            SergeBerney-KinSA

            Hello,

            Thanks for your answer, I recover the file with FileMaker v11 (and it's seems to be better than before). But... yes there's a but :

            After recovery process, FileMaker told me that I MUST NOT use this file as provided, but reimport data on a clean structure.

            Why this message ? What's the problem ? How can I check which fields (on structure) cause problem ?

            What I did : I create (from this recovered file) a Clone copy (without data), and reimport data from recovered file to this new clone and "save compacted" all this new file. Is that OK ?

            Thanks for all your assistance !!!

            Serge Berney

            Kin SA

            • 4. Re: Recurent corruption of a huge database
              philmodjunk

              That error message is intended to tell you that while the data is accessible in the recovered copy, the structure, scripts etc may not function correctly. In fact, best practice is to NEVER use a recovered copy if you have any alternative available to you. If you have a back up copy that is not damaged, you can create a clone of it and then import your data from your recovered file.

              If not, your only alternative may be to rebuild your file as I've previously described. If you have to go that route, you may be able to read the recover log and figure out which parts of your file where damaged. If so, you can take a copy of this damaged file, delete the tables, layouts or other part of your system that were reported as damaged and then recover this copy. If you get a clean recover, you may have at least narrowed down the rebuilding task to a much smaller portion of your entire system.

              For your future protection, set up a system where it automatically backs up your file frequently and in such a way that you have multiple back up copies s going back a long ways (months and years). This can save you thousands of hours of lost time and help protect you from permanently loosing critical data.

              Our invoicing system here, uses FileMaker server scheduled backups to back up the data every hour in unverified backups. Every evening, a verified back up is made and a system script run from an OS scheduler copies these files to a different machine on the network. The boss then takes yet another copy of the system home with him on a flash drive (can't convince him to pay for automated off site data storage or I'd use that.) The nightly back ups are kept for at least 21 days before the oldest is replaced by the most recent. Once a month, I pull one copy from the nightly back ups and store it in a different directory so that I keep one back up a month in permanent, long term storage.

              Oh yes, and if you set up a third party utility to back up your files, be careful to set it up to only back up a closed copy of your files such as a server scheduled back up copy. Attempting to back up an open copy of a FileMaker file can not only corrupt the back up copy, but can also back up the original file as well.