Delaware Finance

Jun 25 2017

SQL Server – copy stored procedures from one db to another – Stack Overflow #db


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.

Written by admin

Leave a Reply

Your email address will not be published. Required fields are marked *