You are restoring a backup of a Firebird 1.5 or 2.0 database to Firebird 2.1 and you see an error similar to this:
gbak: writing data for table xyz gbak:20000 records written gbak: Error: value exceeds the range for valid dates gbak: Error: gds_$receive failed gbak:Exiting before completion due to errors
How do you go about solving the problem? Well first of all a quick visit to the Firebird bug tracker reveals http://tracker.firebirdsql.org/browse/CORE-1714 and a couple of comments from Dimitry Yemanov.
“The error means to say that some column has an invalid date value (outside the supported range). Prior to V2.1, it was possible to store such invalid values in the database, but now it's prohibited. A verbose output should point you to a problematic table.” Also “The current behavior is intended and is unlikely to be changed.”
Firstly use the –v (verbose option of gbak) to find out the table that is causing the problem.
Check the table xyz for date columns
Perform the following SQL operation on all the date columns you found in 2.
isql> update XYZ set MY_DATE_COLUMN = MY_DATE_COLUMN;
At some point during the process you will see the same error that gbak produced, but now you will know which column is causing the problem.
Install Firebird 1.5 or 2.0, by going back to the version of Firebird that allows for invalid dates, you will be at least able to correct the invalid date to something more appropriate.
Now lets check for dates that are outside of their proper range (01 Jan 0001 - 31 Dec 9999)
Below date zero:
isql> select PRIMARY_KEY from XYZ where MY_DATE_COLUMN < '0001-01-01'
if an error occurs correct the date to something more appropriate and meaningful
Maximum date:
isql> select PRIMARY_KEY from XYZ where MY_DATE_COLUMN > '9999-12-31'
if an error occurs correct the date to something more appropriate and meaningful
You can now backup the database using Firebird 1.5 or 2.1 and successfully restore under 2.1