undo changes to a stored procedure

Question

I altered a stored procedure and unknowingly overwrote some changes that were made to it by another developer. Is there a way to undo the changes and get the old script back?

Unfortunately I do not have a backup of that database, so that option is ruled out.

Answer 1

The answer is YES, you can get it back, but it's not easy. All databases log every change made to it. You need to:

1. Shutdown the server (or at least put it into read-only mode)
2. Take a full back up of the server
3. Get a copy of all the db log files going back to before when the accident happened
4. Restore the back up onto another server
5. Using db admin tools, roll back through the log files until you "undo" the accident
6. Examine the restored code in the stored proc and code it back into your current version

And most importantly: GET YOUR STORED PROCEDURE CODE UNDER SOURCE CONTROL

Many people don't grok this concept: You can only make changes to a database; you can't roll back the stored proc version like you can with application code by replacing files with their previous versions. To "roll back", you must make more changes that drop/define your stored proc.

Note to nitpickers: By "roll back" I do not mean "transaction roll back". I mean you've made your changes and decide once the server is back up that the change is no good.

Answer 2

"Is there a way to undo the changes and get the old script back?"

Short answer: Nope.

:-(