Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
pmccann1
Posting Yak Master
107 Posts |
Posted - 2007-09-24 : 05:27:06
|
i have accidentally deleted a stored procedure is there anyway of getting this back, just found out there is no back up |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-09-24 : 05:30:14
|
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 AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-24 : 05:39:36
|
"just found out there is no back up"Create a Maintenance Plan for All User Databases without delay ... |
|
|
pmccann1
Posting Yak Master
107 Posts |
Posted - 2007-09-24 : 05:52:44
|
Can this be restored from a transaction log back up |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-24 : 06:10:38
|
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 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-24 : 06:11:46
|
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. |
|
|
pmccann1
Posting Yak Master
107 Posts |
Posted - 2007-09-24 : 06:26:53
|
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 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-24 : 06:36:45
|
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,RestoreKristen |
|
|
|
|
|
|
|