How to Recover from a Damaged File Using the FileMaker Data Migration Tool

9/11/18 update: I need to recant the information that was provided in this blog post based on what I learned at this year’s DevCon. After talking to a FileMaker engineer about the Data Migration Tool, I learned that using the tool to recover from a damaged file is not a supported use case like I had previously thought. My incorrect understanding was based on information provided during the testing period, but that had apparently changed by the time the 17 platform was released.

Depending on several factors, the tool will run in either block mode (fast) or record mode (slow). The engineer I spoke to explained that when the tool runs in block mode, if data is corrupted, it will most likely end up getting moved into the target file as is. Running the tool in record mode would give a better chance of correcting the damaged portion of the file, but even then there’s no guarantee, because it’s not an official feature of the tool. (Unfortunately there is no command to explicitly instruct the tool to run in record mode.)

As part of the FileMaker 17 release, FileMaker Inc. has rolled out a new tool to make migrating data between copies of a database file much faster and easier. The Data Migration Tool has two primary use cases:

1. Deploy development changes by migrating production data into a development file
2. Revert to a known clean clone by migrating data from a damaged production file

The first use case is likely to receive the most attention. Please see my colleague Matt Hintz’s post for a detailed examination of this use case.

The second use case will probably not garner as much notice. However, if you find yourself in a situation where you have to recover from a damaged file, you will suddenly develop a strong appreciation for the existence of this new tool.

This is exactly what happened to me not too long ago. What follows is an account of how the new Data Migration Tool made it possible for me to quickly address a database corruption issue for one of our clients.

Error 242 Database could not be opened; may be damaged

My client’s server crashed on Tuesday, April 17th at 11:45 am. I learned about the outage about an hour later, thanks to a 12:40 pm FileMaker Server email notification. It said the database was damaged and could not be opened.

Below is the full notification that was sent. Note that, because there are certain details of my client’s environment that I do not want to publicly disclose, I changed the FileMaker host name to “FMS” and the database file name to “FMDB”. (This particular solution consists of just one file.)

• FileMaker Server 16.0.3 on FMS reported the following event:
  2018-04-17 12:39:38.482 -0600 Error 242 FMS Database “FMDB” could not be opened. Database may be damaged; use the Recover command in FileMaker Pro 16.

I checked the Windows Server system and event logs to pinpoint the time of the server crash. Progressive backups had been turned on, so I replaced the database with a progressive backup from 11:44 am – right before the crash. The file opened without any errors at that time.

A few days later, FileMaker Server ran a weekly backup with verification enabled. The schedule reported that the database failed a consistency check. The following two notification emails were sent out:

• 2018-04-22 05:00:20.639 -0600 Error 640 FMS Consistency check failed on backup of database “FMDB”. (805)
• 2018-04-22 05:00:20.654 -0600 Error 664 FMS Schedule “Weekly” completed, but consistency check failed on backups of one or more databases.

I checked in with my client, and he reported that users were still able to use the system. Since it was Sunday, we agreed that I would work on addressing the issue the next day towards the end of the work shift.

When I began my work the next day, I closed the file in the FileMaker Server admin console and then tried reopening it, but it wouldn’t open. At this point, FileMaker Server sent out another notification email:

• 2018-04-23 15:27:53.078 -0600 Error 242 FMS Database “FMDB” could not be opened. Database may be damaged; use the Recover command in FileMaker Pro 16.

Options to recover from damaged database

At this point, I had two options:

• Recover the file and continue using it – not recommended!
• Revert to a last known good backup

Many FileMaker administrators choose the first option, because it is the quickest way to get up-and-running. However, it is NOT the recommended option. There is no guarantee that recovering a file will remove the underlying database corruption. In fact, it’s quite possible that it will correct the problem enough to make it possible to open the file but leave the root cause of the problem in place, unbeknownst to you. File recovery may even end up removing solution elements like layout objects or record data in an attempt to fix the file corruption.

The second option is best practice: revert to the most recent backup prior to the crash. It’s natural to resist this, because reverting to a backup involves loss – data loss (e.g. if you revert to yesterday’s backup, you will lose all of today’s data) and even functionality loss (e.g. if you revert to last month’s backup, you will lose whatever schema, script, and layout changes you made to the file since then). This data and functionality loss can be overcome, but not easily.

Functionality Loss

To address functionality loss, you need to re-deploy all of the changes made since the backup file was created. This might mean having to manually recreate those changes. If you follow the practice of tracking your changes over time, this is possible but still may involve significant effort. But if you do not track your changes, determining the difference between the files can be quite challenging.

In that situation, one tool you might consider using is the third-party software “FMDiff“, which performs a side-by-side comparison of two versions of the same FileMaker file.

Even better, if you manage your solution so that changes are not trickled into production daily but are instead batched into releases, you increase the odds that you’ll have a “last known good backup” with the same codebase as the damaged file. In this case, you won’t face any functionality loss. Be sure to perform a manual backup after each release to help ensure that this is the case.

Data Loss

Until now, in order to address data loss, you would have to import all records for all tables into a clone of the backup to which you are reverting. Even if you already had a script in place that did this, running the script could take a long time. For a solution the size of several gigabytes, a script like this could run for several hours.

However, with the release of the Data Migration Tool, this data import process becomes much easier and much, much faster.

FileMaker Data Migration Tool to the Rescue

In my case, I was dealing with a file that failed a consistency check and wouldn’t reopen. I decided to revert to the last known good backup and then try restoring the data using the new Data Migration Tool. This involves specifying a source file and a target file. In my case, the source file was my damaged file, and the target was a clone of the backup file.

I had previously configured hourly, daily, and weekly backup schedules for my client. The daily and weekly schedules are both set up to create clones of the solution, so I had plenty of clones to choose from. I selected the clone from the weekly March 11, 2018, backup schedule. My last release of development changes was done on March 6, 2018, so I figured this was going back far enough in time to maximize my chances that the database corruption wouldn’t be in the file (as I didn’t know exactly when it got introduced in the first place), but not so far back that my latest development changes would be discarded.

Data Migration Tool (DMT) Take One

I first tried running DMT by migrating data from the damaged file to the clone, but the log showed many errors like this:

• No compatible source found for target field “FieldNameXYZ”

Not all of the fields showed this error, but most did.

That shouldn’t have been the case since the schema should have been identical between the two files. This suggested that the damage to the file was such that FileMaker wasn’t able to read the table and field schema properly.

DMT Take Two

Next, I recovered the corrupted file and tried running DMT, migrating data from the recovered file to the clone. I got this error:

• Couldn’t open the source file because “(804): File cannot be opened as read only in its current state.”

DMT Take Three

I don’t know why that error happened. It seemed like it should have worked, so I tried again. However, before doing so, I opened the recovered file and closed it. I then re-ran the Data Migration Tool the same way once again, and this time it worked. No errors were reported in the log.

I compacted the DMT-recovered file and copied it to the live folder. It opened without errors.
I also ran an ad hoc verify schedule that I already had in place from before, and it did not report any errors. At this point, I considered the file to be fully recovered, and I notified our client that the system was back online. If something like this happens again, I will skip “take one” and proceed directly to recovering the damaged file before using the Data Migration Tool.

As a point of reference, the damaged source file was 1.2 GB in size (126 MB when compressed as a zip file). Running recovery on the file took less than three minutes, and it took the Data Migration Tool just 66 seconds to migrate the data. That’s pretty impressive. Without the tool, I’d estimate that it would have taken two to four hours instead.

Remote Containers

Note that externally-stored containers do not get migrated over with the Data Migration Tool; they need to be copied over manually. In my case, I just left them in the original live folder as-is.

Will DMT Remove Corrupt Record Data?

The Data Migration Tool can help recover from a damaged file, but can it also be used to remove more benign cases of database corruption? For example, can I use it when fields display question marks instead of actual values?

I tried out the tool for another one of our clients who reported the following symptoms:

• Searching for status “X” and location “Y” shows 20 records.
• Three of these records don’t display any values. (When I took the file offline and opened it locally, the fields for these corrupt records displayed question marks instead of blanks.)
• If you delete these three records, FileMaker Pro doesn’t report an error. However, the total record count doesn’t change, and when you repeat the search, the corrupt records display once again.

These symptoms could be describing corrupt record data or corrupt field indexes. I was curious to see if the Data Migration Tool would recognize and fix the corrupt records or indexes. Unfortunately, the issue did not get resolved whenI ran the tool. (I did not recover the file prior to running DMT.)

So, what should you do in this case? Here are some other methods you can try:

Rebuild field indexes:

• Question marks are sometimes an indication of damaged field indexes.
• You can rebuild each field index in the affected table manually.
• Or you can run Recover on the file without actually doing a recovery, simply selecting the option to rebuild field indexes. Select the “copy file blocks as-is” and “rebuild field indexes now” options and leave all of the other options unselected.

Reimport data:

• Arrange the found set to include all records except for the corrupt ones.
• Export the found set using the merge format.
• Write a script to truncate the table. (If you use “Delete All”, and you have cascade delete in place, you may unwittingly delete records from other tables.)
• Import the good data back into the file.
• Import the corrupted records from a backup (if you can find a backup that still has those records non-corrupted).

In my case, out of curiosity, I tried both of these options. Each one successfully resolved the data corruption.

Summarizing the FileMaker Data Migration Tool

In addition to being used as a deployment tool (to assist with the process of releasing development changes to production), the Data Migration Tool can also be used to recover from a damaged file by moving production data into a clone of a last known good backup. What was previously a cumbersome and time-consuming process is now both fast and easy.

Data Migration Tool Overview for Recovery

Here is quick guide to using the Data Migration Tool to recover from a damaged file:

Before the problem occurs (start following these best practices now!):

• Set up backup schedules to create clones of your database files.
• Maintain a reasonably long history of backups and clones. I typically keep 7 daily and 12 weekly backups.
• Enable verification for your weekly, and, if possible, your daily backup schedules.
• Enable email notifications so that you will be informed if a file becomes damaged.
• Keep notes on the changes you deploy to production, both what they are and when they were made, ideally releasing them in batches.

Once the problem occurs:

• If FileMaker Server reports that a consistency check failed, or if you are unable to open the database, copy (don’t move) the damaged file to another folder.
• Recover the file (File menu > Recover).
• Decide on which clone you will use. Go as far back in time as you can without losing any development changes that you may have made to the file.
• Using the Data Migration Tool, specify the recovered damaged file as the source and the clone as the target. If the tool gives an error, open (locally, in FileMaker Pro) and then close the recovered file, and then try running the tool again.

Moving Forward in FileMaker 17

If you have any questions about how to use the Data Migration Tool or any other new features included in FileMaker 17, please contact our team. We’re happy to help your team determine the best way to leverage them within your FileMaker solution.

---

Resources

• Introducing FileMaker 17’s FMDataMigration Command Line Tool (by Matt Hintz)
• Maintaining and recovering FileMaker Pro databases
  • About recovering FileMaker Pro files
    • Preventing database damage
    • Damaged files
    • Damaged layouts
    • Checking file consistency
    • Recovering files
    • Setting advanced file recovery options
• How to Fully and Correctly Use FileMaker’s Recovery Tool (by Makah Encarnacao)
• Comparing differences between two versions of the same file using FMDiff