FileMaker Data Migration

Data Migration. Sounds pretty boring, right? Feels like it will be a chore, right? Talk to any FileMaker developer and you’ll find that being assigned a data migration task will be exactly last on their list of the things that they enjoy doing. What is data migration, exactly? Well, the basic idea is taking all of the data from one FileMaker file and moving it into another FileMaker file. This can happen for a variety of reasons, but the most common is when you need to roll out a new version of your application, complete with all the new features that were tested in your development environment, to the production environment, where it will need to be paired with your live, production data.

For years, FileMaker developers had to do this by hand – developing a scripted process that would move the data from one file to another. Then, a few years ago, FileMaker released the FileMaker Data Migration Tool (which I’m going to refer to as the ‘FMDMT’ for most of the rest of this post) for subscribers of the FileMaker Developer Subscription, which makes that already-useful subscription even more valuable.

The FMDMT makes migrating data much faster… but it is definitely not easy to use, or at least not at first. Why? For starters, all the commands are issued at the command line, so there’s no user interface, which can be a totally new experience for some developers in a low-code platform. Still, fear not! With a bit of guidance (and some tips along the way), you too can perform data migration with the FMDMT.

At this point, the reader might be wondering why I chose to write this blog post now. After all, the FMDMT has been around for quite a while, and there are tons of tools out there that will help you do this. While that may be true, as I executed several of these migrations here at Skeleton Key, and talked through the process with other developers on my team, I gathered several ‘Pro Tips’ and tricks on how to make the entire process and experience easier, faster and better. So, let’s get started!

Demo Assumptions

For the purposes of this demo, I’m going to be using a FileMaker Server I have set up on my local computer (a Mac), and I’ll be moving data from my production copy of ‘Contacts.fmp12’ (our source file) to my development copy of called ‘Contacts_Dev.fmp12’ (our target file). To be super-clear, when I use the term source I’m talking about the file where the data comes from, and when I use the term target I’m talking about the file where the data goes to.

Prep work – Gather all the passwords

Before you get started, there a few things you should gather. I’m assuming that you’re going to do this to a file that is hosted on a FileMaker Server somewhere and is encrypted as well. Eventually, as part of this process, you will need to have the full access passwords for both the files you are moving data from (the source) and to (the target), as well as the encryption passwords (if applicable). Also, you’ll be logging into the admin console of the server a few times during this process, so you’ll want the username and password for the Admin Console of the FileMaker Server as well.

Pro Tip: Save yourself some time and effort later. Go and gather all of those credentials now and jot them down temporarily in your favorite text editor for easy access along the way.

Prep work – Folders and files, oh my!

Unfortunately, you can’t perform a data migration on a hosted file. You’ll have to download copies of the files you’re working with from the server (or servers) and onto your local computer. I tend to put all of the files and folders (aka directories) I’m working on for this process into a designated folder on my desktop for easier access.

Pro Tip: Since much of what you’re about to be doing will involve Terminal commands, and Terminal commands are much easier to type without any spaces in them, be sure to name all of your folders without any spaces in the names.

For this demo, I’ll make a folder called ‘Contacts_DataMigration’ on my desktop. Within that folder, I usually put three additional folders – ‘Originals’, ‘Source’ and ‘Target’. Why do I have an ‘Originals’ folder, you might ask? Well, I like to keep copies of the original files that I’m working with, just in case I mess things up and want to start over. During the data migration process, I’ll put the source file (in this case ‘Contacts.fmp12’) into the ‘Source’ folder and the target file (‘Contacts_Dev.fmp12’) into the ‘Target’ folder.

Pro Tip: While keeping your files in separate folders isn’t necessary for the data migration process, it’s often easy to get the files confused. Having them in distinct folders makes it easier to keep track of which file is which.

On my Mac, the folders look like this:

Prep work – Get the files

The first file you’ll need is the actual FileMaker Data Migration Tool itself. Since the FMDMT is updated regularly, you’ll want to follow the link provided with your subscription and be sure to download a current version. You’ll want to put the FMDMT into your main ‘Contacts_DataMigration’ folder, just for ease of access.

Your main folder tree should look like this now:

Next, you need to get copies of your target and source files. Before you do this, I cannot recommend strongly enough that you make a FileMaker Server on-demand backup of both files. And because I cannot emphasize that enough in a single sentence, I’ll repeat it again here:

Pro Tip: ALWAYS make a backup first.

Once that’s done, close both files (i.e., stop hosting them) and download a copy of both from the server(s) to your local computer. FileMaker Server will download them to your computer as zip files and place them in your downloads folder.

Move the zip files to the ‘Originals’ folder and unzip them both there. Your main folder tree should look like this now:

Now, you have to set up your source and target files. Making the source file is easy – just copy and paste your source file from the ‘Originals’ folder to the ‘Source’ folder. Again, all of this folder structure stuff isn’t strictly necessary, but it helps me keep track of which file is which and protects me from making any simple mistakes.

Making the target file is a bit more work – you need to make a clone of it. Why? Because we need that target file to be empty of existing data so that the FMDMT can do its thing and fill it with data from the source file. Furthermore, the FMDMT is pretty finicky about this clone being unopened, i.e. a pristine clone. Once you make the clone, DO NOT open it before you do the data migration. Why? FileMaker has an internal flag that can tell if a clone has been opened, and the FMDMT will not work with a clone that has been opened up after it has been created. You can, however, rename the file.

Pro Tip: I usually append the word ‘clone’ at the end of the file so I know which file it is. Again, not strictly necessary, but helpful.

Your main folder tree should look like this now:

Congrats! You now have all of your files and folders setup. Now, onto the scary stuff – Terminal commands.

Building your Terminal Command

The FMDMT has a few different options for how you can run it, and creating the command to run it can be challenging. You can find the guide here. What I usually do is assemble it in chunks, using a text editor first, so I can confirm that all the necessary parts of the command are accounted for and are in the correct order.

Overall, the command is separated out into three sections:

1. Source File Parameters
2. Clone File Parameters
3. Other Parameters that affect the process as a whole

Let’s build out our Source File Parameters first.

Pro Tip: On a Mac, with the Terminal app open, if you want the full path to a file or folder, simply drag that file or folder into the window. This is especially helpful because it will automatically add the proper characters to escape any spaces that may be in the path.

The first thing we need to do is give the Terminal the location of the FMDMT tool itself. I dragged the file from my ‘Contacts_DataMigration’ folder into the Terminal window, and this is what it looks like:

While I don’t want to run any commands yet, as I don’t have any parameters set, it’s a good way to start building out the command. I’ll copy and paste just the path and the file name from the Terminal to my text editor:

Now, I need to start building out the Source File Parameters. The first thing I’ll need for this is the path to the source file. I’ll use the same trick that I did with the FMDMT to get the full path:

Pro Tip: For readability, you’ll notice I’m putting each parameter on its own line in the text editor. I’ll remove the line breaks and create one long command string before I actually run the command.

The next parameters don’t require you to know the path to a file, so they are easier to create. You need to set the source file’s username, password, and encryption key (if the file is encrypted).

Pro Tip: I haven’t used my real credentials here of course. We may be friends, but it never hurts to be careful when publishing something online.

Here is the full source section completed:

Next comes the clone, or target section of the command. You have to pretty much do all of the same work you did for the source file here, but for the target.

This is what it looks like when we’ve got all the parts completed (but not yet combined into a single command string):

Pro Tip: Again, while not strictly necessary or outlined in the guidelines for the tool, sometimes we use passwords with all sorts of crazy characters, some of which can be interpreted by the terminal as a command, or an operator, but not your password. This can cause the entire procedure to fail. To avoid this, wrap your passwords and usernames in single quotes, like this:

Next comes the overall parameters which primarily impact what I’ll call ‘structural data’, e.g., aspects of the solution that don’t reside in tables and fields. For example, you can choose to migrate (or ignore and thus not migrate) value lists, user accounts and several other items. Each of these will depend on your particular setup, and what you want to migrate.

The only optional parameter that I would suggest that you always use is the ‘verbose’ parameter. This parameter will provide feedback to the Terminal as the data migration occurs, letting you know what it’s doing as it does it. To enable the verbose parameter simply add ‘-v’ at the end of your list of commands, like this:

Pro Tip: Be aware that even with this option turned on the Terminal may seem to freeze when you are migrating data from very large files. If this happens, don’t panic; it’s still working away behind the scenes.

Finally, make sure you remove all line breaks. This is what my list of commands looks like with all the line breaks removed:

Command Execution

The time has come, my friends. We’re ready to light this candle! Simply c  and paste that entire command into the Terminal, hit the enter key and watch the Data Migration Magic™ happen. If you’ve done everything correctly, you should see verbose feedback start to populate the Terminal window, revealing the play-by-play of the FMDMT at work. The whole thing should end with a summary of the migration.

Pro Tip: Make sure to scan the log and look for anything that indicates an error or that something may not have migrated correctly. In fact, I tend to copy the entire log and save it in my data migration folder as a text file, naming it something like ‘Migration Log’.

There should now be a new file in your source folder. It will have the same name as your source file, but with the word ‘migrated’ appended to the end of the filename. In my example, I now have a new file titled ‘Contacts migrated.fmp12’.

This is what my folders and files look like now:

You can take that ‘migrated’ file, rename it, and upload it to your production FileMaker Server now.

Pro Tip: Be sure to remember to remove your old file (remember that file? The one you closed and downloaded earlier in this post?) from the FileMaker Server first.

In Conclusion

There are a ton of tools out there that will help you build that lengthy command string. If they work for you, that’s great, but personally, I like to get under the hood with things like this, so I can understand what’s going on. Also, those tools don’t always handle escaping spaces very well, and they generally keep you from being able to see the log of what happened.

Pro Tip: Save the whole data migration folder somewhere. In fact, I will often even save the text file with the actual command string that I built in my text editor, so I can run the same process again easily in the future.

And that’s it! In summary, here are the main points I want you to take away from this post:

• Grab all the passwords you’ll need before you get started.
• Make a backup before you begin.
• Organize your files in folders, to keep things clear and manageable.
• Don’t open your clone file after you create it.
• Drag your folders and files to the Terminal to get the full path to each file easily and accurately.
• Wrap your passwords and account names in single quotes.
• Save your work for future reference.

About Skeleton Key

Skeleton Key develops apps on the Claris FileMaker platform making them easy-to-integrate, easy-to-use, and quick to build and deploy. Our team of experts takes a comprehensive consulting approach to focus on learning how your organization operates. With deeper insights into the way your team works, we’re able to create an ideal solution built around your operations while forming a partnership founded on trust and transparency. We hope you found this content useful and we would love to hear from you if we can be of any further assistance.