#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15

    Exclamation Database restore problem


    Hi,

    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:

    Code:
    # 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.


    Szilvi
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15
    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...
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    Originally Posted by nagysz
    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...
    You need first to change SP2 and then to make the backup.
    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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    7

    Thumbs up


    About SP by FirebirdSQL
    -----------------------
    http://www.firebirdsql.org/refdocs/langrefupd21-ddl-procedure.html

    ---------------------------

    Listing all SP and your FIELDS and TYPES

    SELECT *
    FROM RDB$PROCEDURE_PARAMETERS


    SELECT B.RDB$PARAMETER_NAME NOME,

    CASE RDB$PARAMETER_TYPE
    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


    SELECT A.*
    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
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15
    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).

IMN logo majestic logo threadwatch logo seochat tools logo