December 22, 2011 at 11:51 pm
hi folks,
by mistake i have altered a procedure in a wrong way, now i want to get back the old definition of that procedure, is there any way to recover it. i don't have any backup.
any help will be highly appreciated.
December 22, 2011 at 11:56 pm
If you have no backup, and the proc is not in source control, and you did not save the proc definition prior to making your changes, then you can try looking on a different server (e.g. QA or Dev or Prod) to find a copy of the original proc. If that environment doesn't exist - then you will need to try and remember what you changed and undo it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 22, 2011 at 11:59 pm
Try this if you can find something in the Query Column:
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC
December 23, 2011 at 12:01 am
The above thing you have mentioned i don't have anything, and made a lot of changes so its difficult for me to undo it, isn't there any process that i can recover it from transaction log or some system catalog.
December 23, 2011 at 12:03 am
You may get lucky with that query. Keep in mind that the old proc definition is not guaranteed to be in the cache - but it's worth a shot.
I would also make sure you start getting backups - and at least save a copy of the proc prior to committing changes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 23, 2011 at 12:05 am
Hmm and something when you are working with critical things on critical environment, is when you need to change something, just duplicate in this case you SP and rename it just adding 1 behind then work on it and if you are this kind of the situation you can just delete it and removing the 1 from duplicated SP ... ok this is very simple and maybe not professional but can save your day and all extra work that you should do to find the solution!
Another important thing, DBA (DB Devs) without Backups cannot survive!!!
December 23, 2011 at 12:07 am
Dugi (12/23/2011)
Hmm and something when you are working with critical things on critical environment, is when you need to change something, just duplicate in this case you SP and rename it just adding 1 behind then work on it and if you are this kind of the situation you can just delete it and removing the 1 from duplicated SP ... ok this is very simple and maybe not professional but can save your day and all extra work that you should do to find the solution!Another important thing, DBA (DB Devs) without Backups cannot survive!!!
I actually do a similar thing on a regular basis. I copy the proc and make adjustments to the copy and not the original - just and extra precaution I like to take that can save a lot of time.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 23, 2011 at 12:08 am
Another idea, do you still have the query window open from which you made all of the changes?
Try hitting undo a bunch of times from there until it gets back to the original state.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 23, 2011 at 12:17 am
SQLRNNR (12/23/2011)
Another idea, do you still have the query window open from which you made all of the changes?Try hitting undo a bunch of times from there until it gets back to the original state.
It worth a shot 🙂
@ghanshyam: You lose something important today but I am happy for you because you will remember it always. It’s hard learning...
Welcome to DBA / DEV community!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply