November 11, 2008 at 9:02 pm
Hi Sql Server's,
Here the scenario like this , i am using a stored proc that contains some functional business logic ,here i didn't took the backup script and start alter this stored proc. after editing some part of the proc and while executing the same i got some unexpected result. so now i need to keep the stored proc as in the beginning state.
my qtn is this how i am able to revert all these changes , by utilizing transaction log files or is there any other method to revert these changes and keep the stored proc as earlier one.
November 11, 2008 at 9:44 pm
Anu (11/11/2008)
Hi Sql Server's,Here the scenario like this , i am using a stored proc that contains some functional business logic ,here i didn't took the backup script and start alter this stored proc. after editing some part of the proc and while executing the same i got some unexpected result. so now i need to keep the stored proc as in the beginning state.
my qtn is this how i am able to revert all these changes , by utilizing transaction log files or is there any other method to revert these changes and keep the stored proc as earlier one.
Sorry... I'm afraid the original is toast unless you have a tape backup or some such.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2008 at 10:31 pm
Anu (11/11/2008)
Hi Sql Server's,Here the scenario like this , i am using a stored proc that contains some functional business logic ,here i didn't took the backup script and start alter this stored proc. after editing some part of the proc and while executing the same i got some unexpected result. so now i need to keep the stored proc as in the beginning state.
my qtn is this how i am able to revert all these changes , by utilizing transaction log files or is there any other method to revert these changes and keep the stored proc as earlier one.
it is impossible (or maybe very difficult) to restore a log under a different recovery path because of conflicting changes
I'll attempt to illustrate:
Say you have the following chain of backups where Fx is a full backup, and Lx is a log backup:
F1 -> L1 ->L2 ->L3 ->L4
Now, for some reason, you restore the database to F1, and only roll it forward to L2. Now, as time progresses, you have:
F1->L1->L2->L3a->L4a
\->L3->L4
with L3a and L4a being the branch that the live database is on. From here you cannot apply any log backup from the original path, as there are conflicting changes and log entries in the two paths.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 11, 2008 at 11:44 pm
Restoring part of the log will get the proc back?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2008 at 12:01 am
hello.. see i just only wants the earlier code , from the sql server database , there is no demand for that must from log files !
any way sql server have the facility to get back the older state like restore . if any tiger know that plzz enclose the information.
November 12, 2008 at 12:02 am
Anu (11/12/2008)
hello.. see i just only wants the earlier code , from the sql server database , there is no demand for that must from log files !any way sql server have the facility to get back the older state like restore . if any tiger know that plzz enclose the information.
no i dont think that there is System Restore in xp sort of function in SQL.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 12, 2008 at 12:50 am
Anu (11/12/2008)
any way sql server have the facility to get back the older state like restore . if any tiger know that plzz enclose the information.
Yup! Restore a backup that has the procedure in the state you want it.
If you have no backup, you're completely out of luck.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2008 at 1:44 am
there is ... there have to be..
November 12, 2008 at 5:03 am
if you will restore a backup, you will loose the changes after that backup of which restore you will take
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 12, 2008 at 6:32 am
Anu (11/12/2008)
there is ... there have to be..
Yes. Restore a backup.
If you don't have a backup you don't get your data back. That's the point of a backup. SQL doesn't have a undo feature for procedures, it doesn't have system restore points. It doesn't have a magical time machine built in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2008 at 7:00 am
November 12, 2008 at 7:27 am
goodguy (11/12/2008)
I'm not a pro like all the others who have already ventilated this thread, but what I would do in this case is restore a backup on an independent computer and copy the original procedure script that I need to my current DB.
Absolutely. That's the best way to do it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2008 at 6:13 pm
goodguy (11/12/2008)
I'm not a pro like all the others who have already ventilated this thread, but what I would do in this case is restore a backup on an independent computer and copy the original procedure script that I need to my current DB.
That kind of thinking makes you a pro. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2008 at 6:20 pm
I've gotta dog-ear this page... all those in favor of developers having write access to production boxes, raise your hand! 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
November 14, 2008 at 6:12 am
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply