Btrieve and Pervasive PSQL: how to identify it and get your data out

Most people do not know they have Btrieve. It is rarely the product anyone chose. It is the storage engine underneath the accounting package, the dealership system or the manufacturing software that somebody bought in 1996, and it only becomes your problem when the vendor stops answering the phone.

This guide covers how to establish whether you have it, what the files mean, and what your realistic options are for getting the data out. It is written so that you can do the identification yourself, because that is the part where most people get stuck and it does not require anybody to be paid.

How to tell whether you have Btrieve

You are looking for four kinds of evidence. Any one of them is a strong indicator.

1. The data files

Find the folder your application keeps its data in. Btrieve data files usually carry one of these extensions:

• .dat is the most common, and also the most ambiguous, because plenty of other systems use it
• .btr is unambiguous and tells you immediately
• .mkd appears in some installations, from MicroKernel Data

The giveaway is not the extension on its own, it is the shape of the folder: dozens of files, each one representing what a relational database would call a table, with no single database file tying them together.

2. The DLLs

Look in the application folder and in the Windows system folder for any of these. They are the Btrieve client libraries, and their presence is close to conclusive:

• w3btrv7.dll
• wbtrv32.dll
• w1btrv7.dll

3. The service on the server

Open the services list on whichever machine holds the data. You are looking for a service named Pervasive PSQL, Actian Zen, or an executable called w3dbsmgr.exe. If one of those is running, the engine is installed and something is using it.

4. A reference inside the application

Sometimes the only clue is a single file. In one client codebase the entire indication was a file named BTRIEVE.PFI, sitting among the source of a 4GL application whose owner had no idea Btrieve was involved at all. If you have source code, search it for the word "btrieve" and see what comes back.

What Btrieve actually is

This is the part that catches people out, including developers. Btrieve is not a relational database. It is a key-indexed record manager, sometimes described as an ISAM engine. It stores records of a fixed length and maintains indexes over them, and that is very nearly all it does.

What it does not do is know what your data means. A Btrieve record is a run of bytes. The knowledge that bytes 0 to 5 are a customer code, bytes 6 to 45 are a name, and bytes 46 to 49 are a packed decimal balance, lives in the application, not in the database.

That single fact explains almost everything people find confusing about Btrieve:

• There is no SELECT * FROM customers unless something has been added to provide it
• Your reporting tool cannot see the data, because there are no named columns to see
• An ordinary database export does not exist, because there is no schema to export
• Two applications can read the same file and disagree about what is in it, if they were built against different record layouts

It also explains why Btrieve was popular. Skipping the relational layer made it extremely fast on the hardware of the time, and reliable enough that a great many systems built on it have run for thirty years without corruption.

Why searching for help is confusing

The engine has been renamed twice and changed owner several times, so searching the internet returns three products that appear unrelated but are the same lineage:

If your vendor's documentation mentions Pervasive and your server is running something called Actian, those are the same product a decade apart. This matters practically: current Actian Zen will generally still open data files created by much older Btrieve versions, which is frequently the cheapest first move available to you.

DDF files decide how hard this will be

Look in your data folder for three specific files:

• FILE.DDF
• FIELD.DDF
• INDEX.DDF

These are the Data Dictionary Files. They are the relational layer that Btrieve itself does not have, and they map the raw byte offsets in each record to named, typed fields. Some vendors shipped them, many did not, and some shipped them and then let them fall out of date as the application changed.

If the DDFs are present and accurate, your position is good. The data can be read through ODBC or SQL as though it were an ordinary database, with real column names, and a competent extraction becomes a day or two of work rather than a project.

If the DDFs are missing, the record layout has to be recovered from somewhere else, usually the application source code. This is slower and it is still entirely solvable. It is not a reason to be told the data is unrecoverable, and if somebody has told you that, they most likely meant that they personally could not do it.

Four ways to get the data out

Route 1: ODBC or SQL through the DDFs

Where the dictionary files exist, the current engine exposes the data through ODBC with named columns. From there it is ordinary work to pull every table into SQL Server or PostgreSQL. This is the cheapest route by a wide margin and it is the first thing to check for.

Route 2: butil unload

Btrieve ships a maintenance utility called butil. It can unload the contents of a file without needing DDFs at all. What it gives you is the raw records, correctly separated but without field names or types, so you still need the record layout to make sense of the result. Useful as a safety copy and as a way to prove the files are readable before committing to anything.

Route 3: recover the layout from the application

If you have source code, the record definitions are in it. Every language that talked to Btrieve had to declare the record structure somewhere in order to read it. Find those declarations and you have the map. This is the route we most often end up taking, and it is methodical rather than clever.

Route 4: ask the vendor, if there still is one

Worth one phone call. Some vendors will provide an export or the record layouts on request, particularly if you are leaving and they would rather part on good terms. It costs an hour to ask and occasionally saves a fortnight.

What goes wrong during extraction

These are the specific ways a Btrieve extraction produces output that looks fine and is not. Every one of them is worth checking explicitly.

• Packed and binary coded decimal. Money is often stored in a packed format to save space. Read as plain bytes it produces numbers that are wrong but plausible, which is the worst kind of wrong.
• Date formats. Btrieve dates come in several representations and applications also stored dates as integers, as day counts from an arbitrary epoch, or as text. Off-by-one and century errors are common.
• Variable-length and memo records. Notes and description fields are frequently held outside the fixed-length record. Extract only the fixed part and you lose them without any error being raised.
• Alignment padding. Compilers insert padding bytes between fields. A record layout that ignores padding drifts out of alignment partway along, so the early fields read correctly and the later ones are nonsense.
• Character encoding. Files from the DOS era use code pages, not Unicode. Names with accented characters are the usual casualty.

The defence against all five is the same and it is not technical sophistication. It is reconciliation: take a report the business runs every month, and prove the extracted data reproduces it exactly before anybody relies on the result.

What to do first

In order, and the first three cost nothing:

1. Take a copy of the data folder. Whole folder, not selected files, and include anything with a DDF extension. Put it somewhere safe. Everything else can be done from the copy, which means the live system is never at risk.
2. Write down what you found. The file extensions, the DLLs, the service name and version, and whether the DDFs are present. That short list determines the whole approach.
3. Check whether the current engine opens the files. If it does, you have a supported path to the data today and no immediate emergency.
4. Find out whether the source code still exists. If the DDFs are missing, the source is where the record layout lives, and it is worth knowing now rather than in six months.
5. Get one table extracted and reconciled before committing to a full project. If one table can be proven correct against a known total, the rest is repetition.

The thing worth understanding is that Btrieve data is recoverable. The format is documented, the engine still exists and still opens old files, and the record layouts are either in the DDFs or in the application. When somebody tells you the data is locked away forever, what they are usually telling you is that they have never seen it before.