URGENT,how to get the definition of a procedure.

  • ghanshyam.kundu

    Hall of Fame

    Points: 3337

    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.

  • SQLRNNR

    SSC Guru

    Points: 281252

    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

  • Dugi

    SSCoach

    Points: 17998

    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

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • ghanshyam.kundu

    Hall of Fame

    Points: 3337

    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.

  • SQLRNNR

    SSC Guru

    Points: 281252

    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

  • Dugi

    SSCoach

    Points: 17998

    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!!!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • SQLRNNR

    SSC Guru

    Points: 281252

    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

  • SQLRNNR

    SSC Guru

    Points: 281252

    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

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42493

    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 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply