Can we backup all stored procedures in a database?

Question

Can we backup all stored procedures in a database?

gary1

SSC-Insane

Points: 21152
More actions
October 31, 2014 at 2:50 pm

#298015

Hi, is there a way to backup all stored procedures in a database?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

if you just want the stored procedures and no data, probably your best bet is scripting:

From within Management Studio, in the Object Explorer, expand your database, expand the Programmability folder, and select "Stored Procedures" folder. Then press the F7 key to open the Object Explorer Details page. From there, you can select all or as many stored procs as you want, right click, then select "Script Stored Procedure as > Create To > File..."

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

gary1 (10/31/2014)
Hi, is there a way to backup all stored procedures in a database?

Yes... have a look at the sys.sql_modules view. There's more than just Stored procedures there, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

In terms of a "backup" as defined by SQL Server backups, no.

But, as has already been pointed out, there are a number of mechanisms for scripting out the procedures. Might I add, it's a good idea to also then take those scripts and put them into source control. In fact, all your database objects should go into source control.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

Chipping in my 2 cents, using a version / source control is good where it is applicable or possible but when it isn't, a combination of bcp and a simple query might do the job, here is an example of such a query

😎

SELECT

DB_NAME()

,OBJECT_SCHEMA_NAME(ASM.object_id) AS OBJ_SCHEMA

,OBJECT_NAME(ASM.object_id) AS OBJ_NAME

,ASM.definition AS OBJ_DEFINITION

FROM sys.all_sql_modules ASM

WHERE OBJECTPROPERTY(ASM.object_id,'IsProcedure') = 1

AND ASM.object_id > 0;