SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 delete stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pmccann1
Posting Yak Master

107 Posts

Posted - 09/24/2007 :  05:27:06  Show Profile  Reply with Quote
i have accidentally deleted a stored procedure is there anyway of getting this back, just found out there is no back up

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 09/24/2007 :  05:30:14  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Without backup, there is no way to get back deleted stored procedure contents, unless of course, you have version control software (like MS Visual SourceSafe) in place.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/24/2007 :  05:39:36  Show Profile  Reply with Quote
"just found out there is no back up"

Create a Maintenance Plan for All User Databases without delay ...
Go to Top of Page

pmccann1
Posting Yak Master

107 Posts

Posted - 09/24/2007 :  05:52:44  Show Profile  Reply with Quote
Can this be restored from a transaction log back up
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/24/2007 :  06:10:38  Show Profile  Reply with Quote
You can't have a Transaction Log backup unless you also have a Full Backup.

You may be able to use a 3rd party log reader, such as Lumicent(sp?)

Kristen
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/24/2007 :  06:11:46  Show Profile  Reply with Quote
P.S. i.e. you have to have already made a Full backup for the Log to be recording data such that it can now be backed up.
Go to Top of Page

pmccann1
Posting Yak Master

107 Posts

Posted - 09/24/2007 :  06:26:53  Show Profile  Reply with Quote
i have just noticed that there was a full back up deon on the 5th of sep and numerous trans log backups from then can this be restored to last night. have you to include all of it
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/24/2007 :  06:36:45  Show Profile  Reply with Quote
Yes.

Restore to a NEW, SEPARATE, TEMPORARY database.

Script the Sproc from there.

Re-run the script on your real database.

Drop the Temporary database.

You need to Restore the FULL and then ALL Tlog backups after that, in chronological order, UP TO a moment in time just before your accidental deletion. (Your restore can include a TLog script made AFTER the accident, but force the restore to stop at a given moment in time just-before the accident - e.g. where a TLog backup spans the time when the accident happened).

If you are SURE that the code for that Sproc did NOT change after some earlier time, then you only have to restore past that time. So ... you may only have to restore the FULL backup if the Sproc could not have changed later. Unless its ONLY you that could change the Sproc its probably better to restore to the point-in-time just before the Delete.

For syntax of the restore see:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example,Restore

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000