Hi Todd,

> --- In firebird-support@yahoogroups.com, Thomas Steinmaurer<ts@...> wrote:
>>
>>> --- In firebird-support@yahoogroups.com, Thomas Steinmaurer<ts@> wrote:
>>>>
>>>>> --- In firebird-support@yahoogroups.com, Doug Chamberlin<chamberlin.doug@> wrote:
>>>>>>
>>>>>> On 2/29/12 2:50 PM, todderamaa wrote:
>>>>>>> Maybe with the possibility of corruption, I should tell him to exclude
>>>>>>> the database files from backup entirely and only backup the gbk files
>>>>>>> that are created in the evening.
>>>>>> This is the usual way to backup Firebird databases in situations like
>>>>>> your clients have. Operating on the live database files is a quick way
>>>>>> to corrupted backup copies. If you have not experienced corruption
>>>>>> either you have been lucky or have not actually tried restoring from
>>>>>> many of those backup copies.
>>>>>>
>>>>>> The client network admins should be aware of this special case when
>>>>>> databases are being used. For this reason some database vendors have an
>>>>>> API that backup software can use to ensure the database activity does
>>>>>> not interfere with the backup operation. The vendors of backup systems
>>>>>> usually charge extra for these modules that handle various databases.
>>>>>> Firebird, however, does not have such an API.
>>>>>>
>>>>>> Steps for a safe, reliable Firebird backup:
>>>>>> 1) Run GBAK
>>>>>> 2) Copy the GBAK output to your backup media
>>>>>> 3) Periodically restore from the GBAK output to a test database to
>>>>>> ensure there are no errors.
>>>>>>
>>>>>
>>>>> I agree.
>>>>>
>>>>> But with this latest issue, we needed the database file and not the backup. The restore of the backup failed, because we had a stored procedure that was selectable that didn't include a 'suspend' statement. I think this must be something that was allowed in Firebird 2.0 but not with 2.1.
>>>>
>>>> This is another reason to perform a backup/restore cycle when upgrading
>>>> to a newer server version. A sanity check and upgrading the ODS to
>>>> activate all good new stuff. ;-)
>>>>
>>>
>>> We had done backup/restore of this database many times since the upgrade. It seems the problem started when we 'altered' this stored procedure. We had been going through our procedures, removing any UDF function calls that could be replace with internal function calls. Seems the problem started occuring after we altered the procedure on the client site.
>>>
>>> Does this make sense?
>>
>> I think you get into your described situation, when you are using the
>> stored procedure as a selectable SP ala:
>>
>> SELECT * FROM YOURPROC
>>
>>
>> when the SP doesn't have a SUSPEND somewhere.
>>
>> Regards,
>> Thomas
>>
>
> Yes, I know we had a mistake. We had a procedure with Return Variables but no suspend statement. We had another procedure that was doing a Select From the first procedure. The first procedure was updating some data and didn't need to return anything. I think we added return variables when trying to debug an issue.
>
> This all worked with Firebird 2.0 and also with 2.1. We have approx. 70 installations (half using 2.1 and half using 2.0). Everytime we upgraded a client we performed a backup on the old version and restore on the new version. We never had a problem, the procedures have been in place for a while with this issue (selecting from a procedure without a suspend). Our clients all do a Backup and Restore every weekend.
>
> The problem started when we altered the procedures. We removed some UDF's and didn't change anything else. But now the database won't restore. It restored fine before, we did the alter procedure.
>
> Does this make sense?

I think yes. In case of your 2.1 installations. Are you running pre 2.1.4?

This all might be related to:
http://tracker.firebirdsql.org/browse/CORE-3003



--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/
http://www.firebirdsql.org/en/firebird-foundation/