Simple SProc backup question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Simple SProc backup question

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2008-08-18 : 14:00:10

I am having a bit of a brain cramp today. Google and searching here is not helping either. Monday blues?

I am creating a backup of all the SProcs to an off site server (outside of source control) using the following :


SELECT *
FROM dbo.sysobjects
WHERE (type = 'P')
Order by Name

How do I narrow it down to only the user defined SProcs and leave out the system Sprocs?

Thanks,
Chris

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-18 : 14:04:36

Why don't you script your Usp using Generate script task?

SELECT name
FROM dbo.sysobjects
WHERE (type = 'P')and name not like 'sp_%'
Order by Name

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-18 : 23:27:56

>> I am creating a backup of all the SProcs to an off site server (outside of source control) using the following :

The statement you have doesn't backup anything, just gives you name of sps.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-19 : 03:47:13

As said, use Generate Script or http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/13/script-out-procedures-and-functions-part-2.aspx

Madhivanan

Failing to plan is Planning to fail

chrispy
Posting Yak Master

107 Posts

Posted - 2008-08-20 : 08:52:40

quote:
Originally posted by sodeep

Why don't you script your Usp using Generate script task?

SELECT name
FROM dbo.sysobjects
WHERE (type = 'P')and name not like 'sp_%'
Order by Name

Thanks sodeep that got me going in the right direction. I knew it was something simple.

chrispy
Posting Yak Master

107 Posts

Posted - 2008-08-20 : 08:53:36

quote:
Originally posted by rmiao

>> I am creating a backup of all the SProcs to an off site server (outside of source control) using the following :

The statement you have doesn't backup anything, just gives you name of sps.

No kidding? Really? Wow?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources