Using qli to Extract Data into a New Database

Firebird versions up to and including v.1.5 provide a command-line tool called qli that can be used to pump metadata structures and data from an existing database to a new one.

The qli (Query Language Interpreter) tool provides an interface to talk gdml, an internal, non-standard query language from times past, that works with Firebird up to and including V.1.5.

Because it can be scripted, qli might be a useful way to pump the retrievable data from a damaged database to a new one.

The qli executable is in your Firebird /bin directory, and a syntax manual in Adobe PDF format can be downloaded here.

Steps for Pumping Data

If you have tried to repair an older database using the gfix and gbak utilities and, despite being able to connect to it, you are still left with signs of corruption, you may still be able to transfer table structures and data from the damaged database to a new one. Database aliases (defined in aliases.conf from Firebird 1.5 onward) do not work with qli. In the examples below, replace the example paths with the absolute paths to the working databases.

  1. Create an empty database.

    You can do this with isql or another tool: your choice. We just want an empty database that will become the target for the pump operation. Our example target is called new.fdb.

  2. Using isql to query the system tables of the damaged database, create the bones of a script for qli to run later.

    You will need an output directory for the script, e.g. /var/scripts.

    Start isql and connect as SYSDBA (or the owner, if it is not SYSDBA) to the latest restored copy (reborn.fdb) that you ended up with after the analysis and repair procedure. For the purposes of the example, our reborn.fdb (the source of the metadata and data we want to pump) is the employee.fdb database from the examples sub-directory of the Firebird installation.

    SQL> /* divert output to a file */
    CON> output d:\databases\scripts\datamove.sql;
    SQL> select 'define relation tgt.' || rdb$relation_name ||
    CON>  ' based on relation src.' || rdb$relation_name || ';'
    CON> from rdb$relations where rdb$relation_name
    CON> not starting with 'RDB$';
    SQL> commit;
    SQL> select 'tgt.' || rdb$relation_name || ' = src.' || rdb$relation_name
    CON> from rdb$relations where rdb$relation_name
    CON> not starting with 'RDB$';
    SQL> commit;
    SQL> /* revert output to stdout */
    CON> output;

    Now you can quit your isql session.

    In the designated output directory you should now have a script named datamove.sql.

  3. Open the script using a plain text editor and edit it.

    Edit the script so that it looks like the following:

    ready reborn.fdb as src;
    ready new.fdb as tgt;
    define relation tgt.COUNTRY
    based on relation src.COUNTRY;
    define relation tgt.JOB
    based on relation src.JOB;
    define relation tgt.DEPARTMENT
    based on relation src.DEPARTMENT;
    define relation tgt.EMPLOYEE
    based on relation src.EMPLOYEE;
    define relation tgt.PROJECT
    based on relation src.PROJECT;
    define relation tgt.PHONE_LIST
    based on relation src.PHONE_LIST;
    define relation tgt.EMPLOYEE_PROJECT
    based on relation src.EMPLOYEE_PROJECT;
    define relation tgt.CUSTOMER
    based on relation src.CUSTOMER;
    define relation tgt.SALES
    based on relation src.SALES;
    define relation tgt.PROJ_DEPT_BUDGET
    based on relation src.PROJ_DEPT_BUDGET;
    define relation tgt.SALARY_HISTORY
    based on relation src.SALARY_HISTORY;
    tgt.COUNTRY              = src.COUNTRY;
    tgt.JOB                  = src.JOB;
    tgt.DEPARTMENT           = src.DEPARTMENT;
    tgt.EMPLOYEE             = src.EMPLOYEE;
    tgt.PROJECT              = src.PROJECT;
    tgt.PHONE_LIST           = src.PHONE_LIST;
    tgt.CUSTOMER             = src.CUSTOMER;
    tgt.SALES                = src.SALES;

    Save the edited script. Now you are ready to begin the data pumping operation.

  4. Run the pumping script through qli.

    Still in the /bin directory, start qli and run the script:

    ..\bin>qli -u sysdba -p masterkey
    Welcome to QLI
    Query Language Interpreter

    All going well, you should now have the data from your reborn database in the new database. COMMIT the script's work, quit, and you are done. Otherwise, move to the next step.

  5. If the script ends with an exception, commit work and try to retrieve more data.

    Although an exception occurs, it does not necessarily mean you cannot recover some data. Commit the work, then use your favourite tool to connect to the new database and see how far through the second part of the script the exception occurred. From data present or absent in tables, you should be able to figure out which table has troublesome data.

    You can use the familiar /* ... */ syntax to comment out lines in the script that have already executed and rerun the script.


    It is worth reiterating that qli, being an old and deprecated utility, will almost certainly fail under Firebird servers V.2 and above. It is expected, for example, that qli will not be able to pump BLOBs to BLOBs.

Even if the data recovery is successful, non-table metadata, such as views, indices, constraints, triggers and stored procedures, will have to be applied separately. An earlier, metadata-only backup would be very useful here. You may be able to make a metadata-only backup of your reborn database; or extract a metadata script from it using isql.

If the script returned errors so many times that you have decided you have a lost cause, professional analysis and recovery tools and services are available, such as those offered by IBPhoenix or IBSurgeon.