As a first encounter with database archiving, FoxPro is a nice challenge. This is the story of our first database archiving attempt.
The archive institution received a PC as part of a journalist’s archive and amongst other things there was a custom-made database application installed on it. While the PC was still working, it was pretty old and looked like it could die any moment. A Ghost image was created A.S.A.P. but the image was created with a version of Norton Ghost that is not recent enough to convert it in a virtual machine, but we can explore the image. The custom application is created with FoxPro, but the application’s installation program is no longer available. Any attempt we made to try to get it to run on another (virtual) machine failed. We were clearly missing some libraries in obscure places or some registry entries. Given that it is so difficult to get the application to run it was quickly clear that we needed to convert the database into another format for archiving and presentation purposes.
Our first attempt was to get the database loaded into Access, but that was only a partial success. The table relations were nicely recovered and most of the data got transfered nicely into Access but we had issues with character set and datatype conversions. The process requires the installation of a FoxPro ODBC driver which may be a challenge on its own when working in a 64-bit Windows environment. If you are a happy owner of a recent machine with a Windows 64-bit OS and want to convert older databases we strongly suggest you get a virtual machine running 32-bit Windows XP. It will make your conversion life much easier. Windows 7 Professional, Enterprise and Ultimate users can download a XP virtual machine for free at http://www.microsoft.com/windows/virtual-pc/download.aspx. If you decide to live dangerously and go with your 64-bit Windows, here’s a tip: the ODBC driver is 32-bit and is not visible in the ODBC Administration tool. Start C:\Windows\SysWOW64\odbcad32.exe instead. Good luck!
In the mean time we felt disappointed with the FoxPro to Access conversion. It’s hard to setup on a recent PC and the results we got are not satisfactory. The database contained some memo fields. Some of them are pretty large and contained diacritical characters. During inspection of some of the converted data we suspected parts of it were lost. But the main problem we faced was the number of steps we needed to take to get the job done because our goal is to automate the ingest procedures as much as possible. It is also not possible to get a less digital experienced user at the archiving institution to repeat our steps without a lot of help.
At the OPF Dabase Archiving Hackathon we learned that MS SQL server would make things much easier as it has support for importing the data built-in. We were very happy that our case was used for one of the breakout sessions. Installing MS SQL server is not always that easy and we only managed to get the data into SQL Server by installing a ODBC/OLEDB driver for FoxPro. Expect to have similar problems on 64-bit Windows as with Access. After migrating most of the issues we had with the Access conversion also applied to the SQL Server scenario. Thanks to the collegues in the breakout session we did get a result very fast though, even if it was to conclude that converting to the MS SQL Server did not change much. Apparently the FoxPro memo data is the source of most of our problems.
So we took a different approach. FoxPro storage is basically very close to the dBASE and DB2 storage formats and there are many tools out there that to a good job in converting dBASE and/or DB2. We tried converting the database using MIXED and allthough it is supposed to support FoxPro, it did not work. The differences in the main dbf format is well documented and seemed to match with the files of our database perfectly. We looked at the memo data files and the matched the documented format as well. The format documentation is somewhat hidden in the MSDN knowledge base. Here is the link to the latest version: http://msdn.microsoft.com/en-US/library/d863bcf2(v=vs.80).aspx. Be sure to read the updates in the comments as there seem to be errors in the documentation. The DBF file format is best explained on http://www.dbf2002.com/dbf-file-format.html. For the other file formats, the MSDN documentation is your only source.
We will be looking into open source dBASE file readers/converters like the MIXED drivers and adapt/upgrade them to handle FoxPro databases like ours. The next step will be to convert the database into an XML format (SIARD – DBML – anything that is standardized). The XML package will need to be documented and we will be looking for an application that can add the metadata to the package and make a complete SIP package ready to be ingested. The last step will be resurrection of the database into a life database for consultation purposess, depending on the requirements of the archiving institution. Still a long way to go, but at least we made significant progress during the Hackathon.
We learned that converting old databases on a 64-bit Windows platform is a bad idea and using other DB formats as intermediate step is only feasible if your old format is well supported and does not contain any complex data or large text/binary blobs. For the more complex cases we suggest to look into drivers or tools that can read the data and convert to the archival format directly or adapt/create them to make them work.
July 16, 2013 @ 11:12 pm CEST
Hi Arif,
Could the approach in the paper you identified be described as migrating the semantics of the database so that it can be understood in the future? I think that is a very promising idea though there will be inevitable losses in "translation".
There is some interesting conversation in the comments of this blog post about systems migration that you might be interested in also.
Euan
April 26, 2012 @ 8:12 am CEST
Archives New Zealand produced a report on successful database preservation in a virtual machine and emulator. The report lists the necessary steps to perform to transform the disk image into a virtual machine or emulator accessible file.
April 26, 2012 @ 8:08 am CEST
It was a pleasure to read your post as this was one of the use cases we were thinking of, when doing some complete machine archiving experiments at the Archives of New Zealand. Is your institution focusing on a bit similar outcome as it was the MARBL with Rushidies Macintosh computer? Or what is the aim of your institution to do with this composed digital artefact or the collection of numerous different (single) artefacts? Is just the database of interest? What kind of machine the database was running on (“PC” indicates some Windows X86 machine)?
Have you tried to make a blockwise copy with the “dd” or “dd_rescue” by now? If I understood Norton Ghost correctly it would have made a blockwise copy of the harddisk. Have you tried to mount this one in QEMU? It is a really versatile X86 (and other systems emulator) which could run such harddisk copies out of the box. We succeeded in most test cases and had just in one to reinitialize the Master Boot Record (MBR) to boot the transferred machine in QEMU. Or would it even be possible to make the disk image available to the OPF developer community?
Why did you try to convert the database into some other one? To access the data as an archivist or make it available to the general public? Especially the custom application dealing with the database was triggering my attention. I think we will see much more of it in the future as e.g. most of the CMS are “custom applications” using a database as a backend. Same is true as for databases used in offices. Much of the business logic might be “hidden” in these (often proprietary) frontend applications programmed for a special purpose. This leads the discussion into the direction which “knowledge” is to be captured beside the primary database files and how an environment needs to be adapted, “configured” to actually run or access the artefact properly.
February 14, 2012 @ 11:46 am CET
Hi Euan,
Yes, we did also an install of Visual FoxPro 9.0 on a recent PC and were able to load the database and the data looked fine. The original PC has a custom-made application that used the database and an important part of the value of the application are the reports that could be extracted. We did not get these reports working in VFP9. It may simply be an issue of settings, but were are not very familiar with FoxPro so it’s hard to tell. I’m not sure what the export possibilities of VFP9 are, but I’m confident there are options that would allow us to get the data out decently. In the mean time we mainly use VFP9 to verify the results of the conversion.
We are providing archiving and preservation services to a number of small institutions and we expect to get databases in many different formats and complexities. This database may as well be the last FoxPro database we ever archive, nobody knows. Our main goal with this case was to find a workflow and approach that will work for most of the databases we may get. Automation is key in this process as we have limited human resources. Installing and learning a database application for every database that we encounter is not what we aim for. We are aware that a fully automated ingest system for database will not be possible due to the heterogenous nature of the material we will receive, but we try to aim as high as possible and hope to get an as high as possible percentage of it automated.
I agree that there are other ways to give the PC a virtual rebirth, but we do not have physical access to it and have to deal with the ghost image as it is. We certainly learned from this experience that we need to set clear specifications of what the institutions should do in such a case. Lesson learned …
Kris
February 13, 2012 @ 10:43 pm CET
Hi Kris,
This is very interesting. We may be starting some research into Database archiving at Archives NZ this year so the things you are learning are very relevant to us.
Some comments/suggestions on the issues you have had with using a VM:
Have you considered installing an original copy of Fox Pro on a virtual machine or emulated machine and accessing the data that way?
There are copies of foxpro available on Ebay at the moment here (14/2/12).
You would then be able to use the export functionality built in to foxpro to export it to a format that might be more accessible.
Another option is to do an install of the same OS on the original disk image onto a virtual or emulated machine and then copy all of the content from the old disk image over the top of the content on the new disk image and trying to see if it would boot that way.
I can also reccomend re-imaging the disk drive using the “dd” program in linux or “dd-rescue”. This will produce an image that (from my experience) ought to boot reasonably successfully in QEMU.
Euan