none
Cannot backup stored procedure in SQL Server 2008

    Question

  • Dear all,

              When I backup a database from sql server 2008 and restore it to sql server 2008. I found the stored procedure is not restored. How can I also backup the stored procedure in backup operation? Thanks.


    hon123456
    Monday, August 01, 2011 9:27 AM

Answers

  • Thats not true... If you take backup the stored procedure and other objects also gets backedup... So what you need to check is as follows

    (a) Is the stored procedure created int he same database which yoiu have backedup or you have created it in Master or some other db?

    (b) before taking backup do the following to ensure the sp is there in the db

    USE yourdatbasename

    Exce sp_helptext 'yourspname'

    if you are able to see this then you should be able to see the same after restoring backup also

     


    MCITP, MCTS, MCDBA,MCP
    • Marked as answer by hon123456 Monday, August 01, 2011 1:50 PM
    Monday, August 01, 2011 9:53 AM
    Moderator

All replies

  • Thats not true... If you take backup the stored procedure and other objects also gets backedup... So what you need to check is as follows

    (a) Is the stored procedure created int he same database which yoiu have backedup or you have created it in Master or some other db?

    (b) before taking backup do the following to ensure the sp is there in the db

    USE yourdatbasename

    Exce sp_helptext 'yourspname'

    if you are able to see this then you should be able to see the same after restoring backup also

     


    MCITP, MCTS, MCDBA,MCP
    • Marked as answer by hon123456 Monday, August 01, 2011 1:50 PM
    Monday, August 01, 2011 9:53 AM
    Moderator
  • Full backup should take every object from database, you might take the backup before stored procedure created. If you want only SP script then right click SP -generate script and deploy on new location

     

     


    http://uk.linkedin.com/in/ramjaddu
    Monday, August 01, 2011 9:54 AM
  • HI,

    Let us know what backup you have taken and restored?  Ideally  a full backup will be helpful for you to get your stored proc resotred.

     

     

    Regards,


    Ramakrishna | Please mark Answer if the given solution answered/resolved your question, Vote it as helpful if it is.
    Monday, August 01, 2011 9:54 AM
  • Hi Hon, 

    Usually it will not happen like this, I would like to know How you are doing backup and restore operations in detail step by step, so that I can assist.

    When you run backup command it will backup every thing and there is no parameters to specify stored procedure also 

    We can script out the only stored procedures with the Generate Script option 

     

    Follow the below links just generate sps 

    http://www.kodyaz.com/articles/sql-server-script-data-with-generate-script-wizard.aspx

    http://msdn.microsoft.com/en-us/library/ms178078.aspx

    Hope this helps 


    Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.
    Monday, August 01, 2011 9:56 AM
  • Thanks for your help. I found that the stored procedure is in another database. Sorry for my mistake.

    Thanks.


    hon123456
    Monday, August 01, 2011 1:51 PM