Retrieving a deleted stored procedure

[zeeshan13]

Hi All,

I have a serious problem.
On friday I modified a existing stored procedure, which exists on one of my databases on our server.

Now, I dont see that stored procedure. Maybe somehow it got deleted accidently.
The problem is that I dont have any backup. But, I do have the transcation logs, but I dont no, if there's a way to retrieve it.

Is there any way I can retrieve my stored procedure??.

Thanks in adavnce,

 

[SQLDenis]

what about backup the DB
Do a point in time restore (yesterday) to another (new) DB
Grab the proc and generate it in the DB that you need


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[zeeshan13]

SQLDenis,

I would appreciate if you can explain further.

What Backup you want me to do on the current DB (full, deferntial, transc log) ?????

Then please let me know the rest of the steps as well.

Thanks a million in advance....

 

[icemel]

You need to restore a FULL backup to another server.

Then just look for your stored procedure - provided you *have* a full backup from BEFORE the time you deleted the procedure from your database.

Read in BOL on how to use EM to restore a backup to server. It's basically just a matter of applying the backup file to a location (server) you specify.

 

[SQLDenis]

I misread your question I didn't see you did not have a backup already in place

mmmmmmm anything on test, development or UAT servers or possibly in SourceSafe?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[zeeshan13]

Unfortunatelty, I dont have any backup before I deleted the stored procedure. If i had, that would be very easy, but I dont.

Is there any way I can retrieve my stored procedure??.

Thanks in adavnce,

 

[zeeshan13]

SQLDenis,

I dont have anything whatsoever.
The only thing I have is the current DB, with full tranaction logs.

There should be some way to retreive it using transaction logs. What do you think???

Thanks in advance...

 

[SQLDenis]

I am not sure, possibly some o those Log Explorer products out there
I am sure mrDenny or SQLBill will know the answer to this one

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[zeeshan13]

mrDenny/SQLBill ,

Can you please save my life here?

Thanks a million in advance.

 

[zeeshan13]

Still waiting for any feed back on this.

Let put my problem again:

I modified a existing stored procedure, which exists on one of my databases on our server.

Now, I dont see that stored procedure. Maybe somehow it got deleted accidently.
The problem is that I dont have any backup. But, I do have the transcation logs, but I dont no, if there's a way to retrieve it.

Is there any way I can retrieve my stored procedure??.

Thanks in adavnce,

 

[SQLDenis]

try posting in the admin forum you might get a better/faster response

http://www.tek-tips.com/threadminder.cfm?pid=962

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[philhege]

Buy Lumigent Log Explorer.

Your situation is a cautionary tale for having a decent backup plan.

Good luck!

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[SQLBill]

Nope. It's probably gone. If you did a backup now and restored it, you are just backing up the current state. If you had done a full backup sometime before, you could have restored it to another instance and applied the transaction log using STOPAT to stop before the change/delete happened. Then you could have copied the lost procedure to where it should be.

One thing to try....SOMETIMES, we don't double check which database we are saving a stored procedure to. Even though the procedure may say USE mydatabase or CREATE PROCEDURE mydb..myproc, that might not be where it is 'saved' at. I would look for it in the Master database, MSDB, and any user databases you might have. Maybe you got lucky and it was saved in the wrong one.

***By the way, I didn't see SQLDenis's remark and have asked Tek-Tips management to remove the duplicate post.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

One more option. How did you modify it? Did you copy it to your computer, make changes and just run it? Or do you have a copy saved somewhere?

I have a folder on my computer called Stored Procedures. Every procedure I create gets saved to that folder as a .sql file. Maybe do a search of the files to see if it has ever been saved?

-SQLBill

Posting advice: FAQ481-4875

 

[icemel]

Why not just re-write the darn thing? Hopefully it's not too complicated. But if you know what it needs to do, you can always rewrite it.