If you have refreshed a database in Microsoft SQL server and later on noticed that some of the stored procedures are missing because it was created in the old database.

You are in luck if you have a back up of the database. Otherwise, those missing stored procedures are gone.

To restore it from a backup (e.g. tapes backup). You will need another server or machine with SQL server running in order for you to restore the tape backup. When you finished restoring the database, in SQL 2005 or SQL 2008 Server Management Studio do the following steps:

1 – Right-Click on the database that you want to get the stored procedures from
2 – Choose “Tasks”
3 – Select “Generate Scripts”
4 – The “Generate Scripts” wizard will show up, select the “Select specific database objects” option
5 – Expand the “Stored Procedures” row and select the SPs that you want
6 – Click “Next”
7 – Make sure the “Save to file” option is selected. Enter a path to save the script.
8 – Click on “Next” and follow on screen instructions and you will be done

Another way is to use the Object Explorer Details in SQL Server Management Studio. Right-click on a stored procedure and choose “Script stored procedure as” -> “Create to” -> File