stored procedure recovery

[Former Member]

so I am VERY new to sybase, I work a lot with mysql though.

we have an application that uses a sybase db and lo and behold someone ran this on it:

print_log_file("Dropping $spName\n");
open(DELETE, ">/tmp/do-sp-delete-$spName");
print DELETE "if exists (select name from sysobjects where name='$spName' and type='P')\n"
. "drop procedure $spName\n"
. "go\n";
close(DELETE) || die("Cannot close file for write - dropping SP $spName - $!");

***I have a full list of the stored procedures removed.***

so we have file level backups of the server, entire server, but its old and you cant really deploy a new server from the backups like R1 does (bare metal).

So my question is, where are the stored procedures stored in file level, are they even?

How would I go about recreating these stored procedures from a file level restore?

Alternatively, *wink wink* dont (we totally do) have access to another server that has these stored procedures on it that I need, how would I dump just the stored procedures from that server and import them on this one?

[tilman_model-bosch]

Stupid me :

Running ddlgen on OS command prompt will also get you that infortmation into an SQL file pretty ready for use:

ddlgen -S<ASE_SERVER> -U<ASE_USER> -D<DBNAME> -TP -N<RPOCNAME> -X -o DDL_PROC.txt

you will only need to addapt servername and perhaps dbname in the output file

Rgds

TIlman

[tilman_model-bosch]

I understand you just need to re-create the procedures in the database . There is another ASE server where these procedures exist in a database. But you do not have the source SQL code of these procedures anywhere around in a plain text file.

Log on to that other server , run

sp_helptext <procname>

for all the procedures , save the output to plain text file. It will need some massaging, but after wards can be used to re-recreate the SPs in the server in the database where these have accidentally beeen dropped.

You might also have to fix permissions etc for the newly re-created SPs - run

sp_helprotect <procname>

on the other server to get that information

HTH

Tilman Model-Bosch