Late one but gives more details that might be useful…
Here is a list of things you can do with advantages and disadvantages
Generate scripts using SSMS
- Pros: extremely easy to use and supported by default
- Cons: scripts might not be in the correct execution order and you might get errors if stored procedure already exists on secondary database. Make sure you review the script before executing.
- Pros: tools such as ApexSQL Diff (this is what I use but there are many others like tools from Red Gate or Dev Art) will compare two databases in one click and generate script that you can execute immediately
- Cons: these are not free (most vendors have a fully functional trial though)
- Pros: You can easily see which stored procedures exist on secondary server and only generate those you don’t have.
- Cons: Requires a bit more SQL knowledge
Here is how to get a list of all procedures in some database that don’t exist in another database
I originally found this post looking for a solution to copying stored procedures from my remote production database to my local development database. After success using the suggested approach in this thread, I realized I grew increasingly lazy (or resourceful, whichever you prefer) and wanted this to be automated. I came across this link. which proved to be very helpful (thank you vincpa), and I extended upon it, resulting in the following file (schema_backup.ps1):
I have a .bat file that calls this, and is called from Task Scheduler. After the call to the Powershell file, I have:
That line will go thru the directory and drop the procedures it is going to recreate. If this wasn’t a development environment, I would not like programmatically dropping procedures this way. I then rename all the stored procedure files to have .sql:
And that iterates through all the .sql files and recreates the stored procedures. I hope that any part of this will prove to be helpful to someone.
answered Feb 20 ’14 at 19:53
I m liking this. I have to write a process for archiving chunks out of a productions DB one year at a time. I don t want to have to have SQL files hanging about which probably won t get updated as the schema develops so I m adapting this to create an empty DB based on a target without the intermediate step of writing files to disk (more to clean up). I reckon this is probably the best and most re-usable answer on this question, kudos sir! Steve Pettifer Apr 24 ’14 at 11:18
You can use SSMS’s “Generate Scripts. ” function to script out whatever you need to transfer. Right-click on the source database in SSMS, choose “Generate Scripts. “, and follow the wizard along. Then run your resultant script that will now contain the stored procedure create statements.