October 17th, 2012, 04:48 AM
Database restore problem
i have this old kind of problem that i saw many times on the forum, but rereading them did not give me the answer i'm looking for.
The short story:
I have an SP (B) that uses another SP (A). I have added a new input parameter to SP A (a month ago). And looks like i did not recompile SP B. That was my mistake #1.
Unfortunately i did a mistake #2: i restored the database from the morning backup (with overwrite option of course )
And at restore i got the following:
# fbrst /tmp/p.fbk test -REP
gbak: ERROR:invalid request BLR at offset 297
gbak: ERROR: Input parameter mismatch for procedure SYS$MENU_FULL
gbak:Exiting before completion due to errors
The domains, tables (with data), generators, exceptions, UDFs, roles, indices are all back...but my triggers and SPs are all missing now.
I tried GBAK -M and GBAK -N but didn't help.
I need to restore my SPs without validating them, then i'll correct that parameter issue and backup it correctly.
I know there i can find all my SP contents in text format in the fbk file....but i will commit suicide if i have to restore my 1200 SP+triggers that copy+paste way.
Please save my life and help me! There must be an easy way to correct this.
October 17th, 2012, 07:01 AM
Thank God i had a file level backup too, so i have my database recovered without any loss.
But still I'm interested in the solution of the problem above. Just in case...
October 18th, 2012, 02:30 AM
You need first to change SP2 and then to make the backup.
Originally Posted by nagysz
After the restore with that error - usually the tables and the data in them is OK - but you may have missing Stored procedures/ triggers and other database objects - at least their bodies. The stored procedure will be there as name and parameters but the body will missing. And you have get them from somewhere - as DDL from another database or some script files.
Last edited by mIRCata; October 18th, 2012 at 02:39 AM.
October 18th, 2012, 10:01 AM
About SP by FirebirdSQL
Listing all SP and your FIELDS and TYPES
SELECT B.RDB$PARAMETER_NAME NOME,
WHEN 0 THEN 'INPUT'
WHEN 1 THEN 'OUTPUT' END TIPO
FROM RDB$PROCEDURES A INNER JOIN RDB$PROCEDURE_PARAMETERS B ON
(A.RDB$PROCEDURE_NAME = B.RDB$PROCEDURE_NAME)
WHERE A.RDB$PROCEDURE_NAME = :NOMEDAPROCEDURE
FROM RDB$PROCEDURES A
This way, you can know which procedures and its contents. this way, you can delete them and re-create them again after backup / restore
October 20th, 2012, 06:37 AM
I don't see how the replies above are related to my question.
The question was about restoring a damaged backup file....so i have no FDB file but only a FBK file (which is damaged by a mistake described in the first post).