locked
Recover DB object RRS feed

  • Question

  • Hi All,

    Could you please tell me that how to recover stored procedure in sql server 2005 without having backup file?

    Thanks in advance.

    Monday, October 8, 2012 8:31 AM

Answers

  • If you don't have Source control and don't have a backup of the database, then you will not be able to restore to the previous version of the stored procedure.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by Joon84 Wednesday, October 10, 2012 7:17 AM
    Wednesday, October 10, 2012 4:17 AM

All replies

  •  Restore the db under another dbname script the sp and execute script.. 

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, October 8, 2012 8:34 AM
  • Hi,

    1.Right click the database and go to Task->Generate Script.

    2.Select the option Storeprocedure and then click Next

    3.Click advance button on the screen,then select Script DROP and CREATE in the options

    This is the back up of SP which can be recovered to any server


    ganeshk

    Monday, October 8, 2012 10:36 AM
  • Thanks .

    But Actually by mistake I altered one stored procedure which i needs to revert to previous version.

    But I dont have backup file of the database.


    Thanks & Regards, Girish Rokade


    Monday, October 8, 2012 10:42 AM
  • If you don't have Source control and don't have a backup of the database, then you will not be able to restore to the previous version of the stored procedure.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by Joon84 Wednesday, October 10, 2012 7:17 AM
    Wednesday, October 10, 2012 4:17 AM
  • But Actually by mistake I altered one stored procedure which i needs to revert to previous version.

    But I dont have backup file of the database.


    SQL Server does not keep automatic backups like Word.

    Next time instead of ALTER, use sp_rename to rename the stored procedure with a prefix "zold". "z" moves it at the end of the list, out of sight. You can delete it 3 months later. A very simple version control method.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Tuesday, October 16, 2012 10:18 PM