+1 on this approach
RefreshFM is a good product. If you want a "free" approach, you can just write scripts to do this. I use three:
1) Show All Records in all tables (that need migrating). Run this in the old file.
2) Delete All Records in all tables. Run this in the new file (that has the new schema).
3) Import All Records in all tables. Run this in the new file.
Couple of points:
1) Use Matching Field Names on the imports to grab the fields easier. (You'll have to save the script after each import when you build it or after the schema have changed, so I leave the dialogs on to verify things are working right.)
2) If you're using serial numbers, you'll need to incorporate updating them into the script. Use Set Next Serial Value.
3) It's usually easier to do this if you have "maintenance" layouts that reference back to the tables you need to migrate.
This is more work than using a good product like RefreshFM, but ... well, it's cheaper.
I would add to that:
- print out the record count in the old files and the new files automatically at the start and the end of the process respectively
- print out the serial numbers in the old file and the new files automatically
This can be fully automated but takes a bit of set up.
We did a lot of data migrations for a huge client and realized that the more you automated the less chances you have of forgetting something. We also had a complete procedure including setting the hosted files to allow only the number of connections that we required for the data dumps, making sure no other users were connected, disabling any server-side schedules or backups, taking backups prior, during and after the data migration to avoid having to redo the whole process.
As you an see there is a lot more than just transferring the data to make this effciient. I have to admit that without the procedure and even though we were doing the process every 3 weeks or so, I would consistently forget some crucial steps. Most notably disabling the server-side schedules (we had a number of those that would inspect data and send out emails, or roll forward changes to another server).