Subject Re: GBak does not fix problem....
Author Adam
--- In, "phil_hhn" <time_lord@h...>
> Hi, using Firebird 1.5.
> We had a corrupt database which we could not backup/restore - it
> complained that there were errors in table BLAH. So we ran GFix and it
> reported that it found (and removed(?)) 3 corrupt records in table
> BLAH. Great. However the backup/restore still fails when it gets to
> this table.
> Has anyone had similar experiences (or have suggestions)?
> Thanks

Yep, Last Thursday ;)\

I thought I was having a mental blank but it turned out gfix was
unable to repair it. It would be nice if it let you know that rather
than just completing and giving you the same error next time you
validate, but that is a topic for another list.

In a nutshell, it was better for us to restore a backup from that
morning than to attempt to repair it, but as an exercise I did repair
it on Friday. For our customer, little action occurred between 4am and
lunchtime when the corruption occurred, and they were about to enter a
peak usage timeframe, so priority was to get it back online and
manually re-enter stuff from the morning (which took 30 mins, boy that
was fun). A side note, the other half of Friday was spent fighting
Word whilst trying document the recovery procedure.

Anyway, background aside, this is how I corrected it on Friday.

Before doing any of this, ensure you are the only user of the
database, and make a file copy which you work on, leave the original
in tact just in case you break something.

1. Run gbak -v and identify the table it fails on.
2. Use iSQL to select all records to confirm the connection bombs out.
3. Re-start iSQL and run something like

select ID
from MyCorruptTable
order by ID

Note the record it bombs at. Assume record 1919 (which it was in my case)

4. Re-start iSQL and run something like

select ID
from MyCorruptTable
where ID <= 1918
order by ID

works fine

select ID
from MyCorruptTable
where ID >= 1920
order by ID

works fine

select ID
from MyCorruptTable
where ID = 1919

internal gds blah blah. Yep, thats the one.

5. Do a metadata only backup (-m), and restore it. This will provide
your clean structure to which we will pump this corrupt db into.

Working with the corrupt database still:

6. Create a table with the same structure as your corrupt table
(tmpMyCorruptTable) will do nicely.

7. iSQL again,

insert into tmpMyCorruptTable
select *
from MyCorruptTable
where ID <= 1918
or ID >= 1920;


Should work fine.

8. Pump your data into the clean structure. I used IB Datapump from I am sure there are others equally as capable.

The only "trick" is to adjust the automatic map to use
tmpMyCorruptTable --> MyCorruptTable.

9. Once the pump is complete, you may need to manually delete records
that depended on record ID 1919 from the clean structure.

10. Backup and restore the clean structure to confirm the error has
been resolved.


I did mess around with QLI and a couple of database repair tools with
no success. I have used gfix successfully in the past (only once,
corruptions are in my experience rare and usually hardware related).
I doubt my one was hardware related because the same server is hosting
about 15 other Firebird databases and none have ever had an issue, but
we are certainly watching it closely.