| Subject | Re: Validate and backup ok, but restore with errors |
|---|---|
| Author | Adam |
| Post date | 2006-02-19T22:30:59Z |
--- In firebird-support@yahoogroups.com, "Francisco Ruiz"
<gruposfcoruiz@...> wrote:
simply writes the metadata information and the data. When it restores,
it creates the tables, inserts the data, and then indices etc. (May
not quite be that order). If your constraint has been broken by the
data, then it will not restore properly. The normal way data finds its
way in there that breaks these constraints is either:
1. The data was there first, and Firebird allowed the constraint to be
added. (IMO it shouldn't, but it does, watch carefully for adding not
null fields to existing tables etc).
2. Some bug in Firebird. (For example old versions of FB/IB allowed
the primary key index to be deactivated, causing duplicates etc).
The rule is that you need to backup your database before making any
DDL changes. You also need to backup after the DDL changes. By backup,
I mean backup then have a sucessful test restore to prove it works.
Btw, validate just confirms the physical structures in the database
are all good. It does not check for broken constraints as you have here.
To recover your database (presuming you have all the data there), find
the piece of data that breaks the constraint (hint: Look at the
specific constraint that fails to create). Adjust the data so it is
correct. Do a metadata only backup then restore to get the correct
table structure. (This should not be a problem). Then do a datapump
into the clean structure.
Of course if you still have the original database (even better), shut
down the service and take a file system copy of it. On the copy,
locate the bad data as above and correct it there. Then backup-restore
and locate the next bad data, correct it, repeat the process until it
works.
To FB Gurus, is there a way of doing a metadata only restore from a
database that contains data as well? This would be a useful feature
for data pumping if you had to restore one at a time, then correct,
then pump.
Adam
<gruposfcoruiz@...> wrote:
>Yes. The backup does no validation of what is in the database. It
> Hi
>
> I have a FB 1.5.2 database. I use IBExpert for FB services.
> If i try a validate database (validate full), no errors are found.
>
> I make a backup (+ garbage collection) without errors.
>
> But now, I try to restore it into a new database (commit after each
> table). Last info lines are :
>
> ..........
> IBE: Arithmetic overflow or division by zero has occurred.
> arithmetic exception, numeric overflow, or string truncation.
> IBE: Restore completed. Current time: 19:39:02. Elapsed time: 00:00:16
>
> and database is not well restored.
> The restore stops when they reach one table; records are restored but
> index are not restored (i don't see them in messages).
>
> What can i do?
> Do i need always to make a Restore after a backup to confirm database
> is not corrupt?
simply writes the metadata information and the data. When it restores,
it creates the tables, inserts the data, and then indices etc. (May
not quite be that order). If your constraint has been broken by the
data, then it will not restore properly. The normal way data finds its
way in there that breaks these constraints is either:
1. The data was there first, and Firebird allowed the constraint to be
added. (IMO it shouldn't, but it does, watch carefully for adding not
null fields to existing tables etc).
2. Some bug in Firebird. (For example old versions of FB/IB allowed
the primary key index to be deactivated, causing duplicates etc).
The rule is that you need to backup your database before making any
DDL changes. You also need to backup after the DDL changes. By backup,
I mean backup then have a sucessful test restore to prove it works.
Btw, validate just confirms the physical structures in the database
are all good. It does not check for broken constraints as you have here.
To recover your database (presuming you have all the data there), find
the piece of data that breaks the constraint (hint: Look at the
specific constraint that fails to create). Adjust the data so it is
correct. Do a metadata only backup then restore to get the correct
table structure. (This should not be a problem). Then do a datapump
into the clean structure.
Of course if you still have the original database (even better), shut
down the service and take a file system copy of it. On the copy,
locate the bad data as above and correct it there. Then backup-restore
and locate the next bad data, correct it, repeat the process until it
works.
To FB Gurus, is there a way of doing a metadata only restore from a
database that contains data as well? This would be a useful feature
for data pumping if you had to restore one at a time, then correct,
then pump.
Adam