4

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.

Raphra
  • 41
  • 1
  • 1
  • 2

8 Answers8

10

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.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • @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. – Bohemian Jul 19 '11 at 01:04
  • 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. http://msdn.microsoft.com/en-us/library/ms189275.aspx – ta.speot.is Jul 19 '11 at 01:31
3

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

Short answer: Nope.

:-(

Michael Ames
  • 2,607
  • 1
  • 16
  • 22
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.

ta.speot.is
  • 26,914
  • 8
  • 68
  • 96
  • 3
    There is no execution plan for DDL. – squillman Jul 18 '11 at 22:09
  • 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. – Aaron Bertrand Jul 18 '11 at 22:47
3

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

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
2

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

squillman
  • 13,363
  • 3
  • 41
  • 60
1

I'm way late to the game on this but I did this same thing this morning and found I had forgot to save my script at some point in the past and needed to recover it. (It will be in source control after I get done fixing this!!!)

Some people mentioned restoring from a backup but no one really mentioned how easy this is if you have a back up. Moreover, you aren't locked into rolling back the production database. I think this is key and assuming you have a back up I would say this is a much better alternative to what has been voted up to the best answer.

All you have to do is take your back up and restore it to a new database. Pull out the sp you are looking for and voila, you've recovered the missing code.

Don't forget to drop the newly created database after you've recovered the missing file.

phipywr
  • 54
  • 1
  • 1
  • 6
0

I had the same problem, and I don't have the confidence to go restoring from log files to another server. I was pretty distraught until I realised the solution was very simple...

Press Ctrl-Z over and over until I had undone my changes and the run the ALTER PROCEDURE again.

Admittedly I was pretty lucky that I still had it there to revert to but it really is the easiest fix. Probably a bit late now though.

Jack Holmes
  • 1
0

If you have scripted the stored procedure out from management studio object explorer this will work. Before expand and collapse the object explorer just scroll and point to the stored procedure you have opened. Script the stored procedure as create or alter to then you can get the previous version of the proc since the object explorer doesn't refreshed yet. This is always my life saver.

JameSQL
  • 89
  • 1
  • 5