Web Development

General discussion


Copying Numerous Stored Proc by Script

By smittyrt ·
I'm a novice SQL admin and I'm wanting to backup some databases to a flat file. To do this I need to copy quite a few stored procedures from the msdb database to each db I want to backup. How can I do this quickly? Right now I'm just copying and pasting in each database which is ineffecient. Scripting this would save me tons of time. Any help is very appreciated!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by Jaqui In reply to Copying Numerous Stored P ...

why do you need to copy the stored procedures?
save the stored prodedures separately, then you don't use space needlessly.

all you need to do is dump the procedures with complete sql inserts.

run it as a qurey on each db.


Collapse -

by Greg Griffiths In reply to Copying Numerous Stored P ...

You have a couple of choices here :

1. If your SP's are in the MASTER db on each server, then you can just grant permissions to them so they can be called from any DB on the same server.

2. You could do point 1 using Linked SQL Servers, so you'd only have to have the code on a single db.

3. If you use the SCRIPT DB option you can select the SP's you want and SQL Server will automatically script them up for you with all the associated DROPS etc.

Related Discussions

Related Forums