I'm having 2 databases in 2 different servers(.246 and .252). I need to create backup for all stored procedures, triggers and function from 252 server and restore it in 246 server.database name in both server is transcredit. around 500SP are there in 252 server. can anyone help me?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
I guess you have framed your question to somewhat misleading.
You want to script out all stored procedures, triggers and functions from one server and create them on the new server.
Option 1 : USING SSMS
OPTION 2 : USING TSQL -
If you have access to visual studio or a 3rd party tool like Redgate schema compare your can run a comparison of the databases and select the objects you want copied from DB to another. The tool will build a script to recreate those objects in the 2nd DB.
Visual Studio DB chema compare here
Redgate Schema Compare here
I have create following Procedure which goes thru all SP and Views in particular DB (can be extend to Functions, ...) and store each code script one by one into TXT files. Tested ON MS SQL 2008 R2 and 2014
First part Inserting all scripts of SP and Views into Temp table. Then later using BCP utility. If you wish you can can use for export SSIS package and not SP as I did in this example.
Loop which goes thru Global temp table creating file name with Prefix P_ or V_ and with suffix as Date in format YYYYMMDD: