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.

Solution 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.

Solution 2

In addition to the sound advice to either use a backup or recover from source control (and if you're doing neither of those things, you need to start), you could also consider getting SSMS Tools Pack from @MladenPrajdic. His Management Studio add-in allows you to keep a running history of all the queries you've worked on or executed, so it is very easy to go back in time and see previous versions. While that doesn't help you if someone else worked on the last known good version, if your entire team is using it, anyone can go back and see any version that was executed. You can dictate where it is saved (to your own file system, a network share, or a database), and fine-tune how often auto-save kicks in. Really priceless functionality, especially if you're lazy about backups and/or source control (though again, I stress, you should be doing these things before you touch your production server again).

Solution 3

You could look through the cached execution plans and try to find the one where your colleague made his changes and run the relevant parts again.

EDIT

Although Bohemian looks to have a good answer if you've got the changes in the TL, this is what I'm talking about. Review the SQL text for the plan.

SELECT  cached.*,
               sqltext.*
         FROM  sys.dm_exec_cached_plans cached
  CROSS APPLY  sys.dm_exec_sql_text (cached.plan_handle) AS sqltext

But as squillman points out, there is no execution plan for DDL.

Solution 4

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

Short answer: Nope.

:-(

Solution 5

You won't be able to get it back from the database side of things. Your options at this point are pretty much limited to 1) recover from backup, 2) go to source control or 3) hope that someone else has a copy still up in an editor somewhere or saved to a file.

If neither of these are an option for you, then here's the obligatory "you should take regular backups and use source control"....

View more solutions

Comments

[Randy]

any source control system? does the other guy have a copy?

[Randy]

also, life lesson. NEVER develop directly on your PROD system

[contactmatt]

Don't forget to say you're sorry!

[Brandon Moretz]

For future reference, msdn.microsoft.com/en-us/library/ff678491.aspx

[Michael Ames]

Let me suggest, however, that programmers are easy to bribe. Offer to fund his/her caffeine addiction for the next month, as penance, and you'll be in fine standing once again.

[squillman]

There is no execution plan for DDL.

[Adir D]

How is the execution plan going to help? Especially if the new version of the stored procedure has been executed? Plans are per statement... there is no magic plan for an entire stored procedure that will let you reverse engineer to get the code that generated the plan.

[Bohemian]

@todda to your first question, I don't know for sure because I haven't used SQL Server, but all DB's log every change. It's a fundamental requirement of DBs to be able to roll back in time through the logs to undo damage. There must be a way.

[ta.speot.is]

SQL Server has "simple", "bulk" and "full" TL models. In simple mode the transaction log file only contains active transactions. Once a transaction is committed the space reserved for this transaction in the log file is freed. This permits "roll back" of unsuccessful transactions or if the server is rebooted there is enough information to roll all active transactions back. msdn.microsoft.com/en-us/library/ms189275.aspx