Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
So are you asking how to create a backup of just those objects and not any of the data in the database? –  James Anderson Jun 10 '14 at 10:29
both servers having the same tables. no need to retrieve data's. just have to create backup for these 3 things and restore it in another server(i.e) 246 server –  Purushothaman Jun 10 '14 at 10:31

2 Answers 2

up vote 3 down vote accepted

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

enter image description here

enter image description here

enter image description here

OPTION 2 : USING TSQL - sys.sql_modules with sys.objects

share|improve this answer
i did like this only. after these process in save or publish scripts page error throwing. i have around 500 SP in this database, is this a problem? –  Purushothaman Jun 11 '14 at 11:21

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

share|improve this answer
i tried this..Is there any query to create backup for all the SP's in a Database? –  Purushothaman Jun 10 '14 at 11:09
and what happened when you tried this? –  James Anderson Jun 10 '14 at 11:54

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.