Backing up multiple stored procedures into individual text files

Question

Backing up multiple stored procedures into individual text files

Noman Salim

Old Hand

Points: 324
More actions
December 12, 2007 at 10:14 am

#119358

I'm using sql server 2005 and I need a way to back-up 200+ stored procedures into individual files with the same names as the stored procedures (basically a back-up of all stored procedures individually) at the same time and not one by one.
Please help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Karthik-214069 SSChasing Mays Points: 631 More actions · Answer 1

[font="Verdana"]Right click on the database in SSMS. Go to Tasks->Generate Scripts.

Go with the defaults in the wizard till you come to the page to select object types. Select Stored Procedures. In the next screen, select Script to File -> File Per Object.

Hopefully this is what you needed![/font]

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 2

I'd use Karthik's solution and then be sure you get them into a version control system. Ideally you are checking them out of some SCC and then editing them before checking them back in.

If you don't have some SCC, you should get one, but at the least, zip up the procs into a file with a timestamp so you know which versions are available.

Noman Salim Old Hand Points: 324 More actions · Answer 3

Karthik (12/14/2007)
[font="Verdana"]Right click on the database in SSMS. Go to Tasks->Generate Scripts.
Go with the defaults in the wizard till you come to the page to select object types. Select Stored Procedures. In the next screen, select Script to File -> File Per Object.
Hopefully this is what you needed![/font]

SWEET! This is exactly what I was looking for. My only problem was that I was trying it from my client machine and I couldn't find the 'File per Object' option. Then I logged into the DB server and there it was...

Muchas Gracias Senor!:w00t:

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 4

Noman Salim (12/14/2007)
Karthik (12/14/2007)
[font="Verdana"]Right click on the database in SSMS. Go to Tasks->Generate Scripts.
Go with the defaults in the wizard till you come to the page to select object types. Select Stored Procedures. In the next screen, select Script to File -> File Per Object.
Hopefully this is what you needed![/font]
SWEET! This is exactly what I was looking for. My only problem was that I was trying it from my client machine and I couldn't find the 'File per Object' option. Then I logged into the DB server and there it was...
Muchas Gracias Senor!:w00t:

This option was added with SP2 if I remember correctly. Most likely SP2 has been installed on the server but the client tools on your machine aren't updated. Run the service pack opn your client and you should have this option there too.

[font="Verdana"]Markus Bohse[/font]