undo changes to a stored procedure

sql sql-server-2005 stored-procedures recovery data-recovery
Raphra picture Raphra · Jul 18, 2011 · Viewed 24.3k times · Source

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

Bohemian picture Bohemian · Jul 19, 2011

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

Most people don't "get" this concept: You can only make changes to a database; you can't roll back the code version like you can with application code. To "roll back", you must make more changes and drop/define your stored proc (or whatever).

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

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More
How to detect if a stored procedure already exists

I have to write a deployment script which will work if a stored procedure exists or does not exist. i.e. if it exists, then I need to alter it, otherwise create it. How can I do this in the …

Read More