In SQL 2000 there was the ability to script out database objects with SQL code. I saved the files and copied them to my 2005 server and it works there. Maybe MS has included the files again in more current versions, or there is another way to do it ??

Anyway, my process is to bring these files onto the server and put them in a folder (hardcoded later) C:\ObjectScripts\:

scptxfr.exe, scptxfr.rll, scriptin.exe, sqlresld.dll

Then I built a routine to script out all the objects from all user databases into their own folders. I find this useful as a way to save all the object definitions such as stored procedure code, table definitions, triggers etc as another level of backup, without having to restore an entire database. Not sure if it could be used to just script SPs.

declare @DatabaseName varchar(100)

declare @code varchar(1000)

declare @DBCount int

-- Load non-System Database names into temp table

select name, ' ' as Run into #programs

from master.sys.databases

where database_id > 4 -- non system

order by name

NextDB:

-- Get database name to process

set @DatabaseName = ' '

set @DatabaseName = (select top 1 name from #programs where run <> 'Y')

--- Script out Database objects

set @code = '"C:\ObjectScripts\scptxfr.exe" /s DB07 ' + ' /d ' + @DatabaseName + ' /I /F '

+ 'I:\ObjectScripts\' + @DatabaseName + '_structure_'+ convert(varchar(8),getdate(),112) + '' + ' /q /A /r'

EXEC master..xp_cmdshell @code

-- Mark database as processed

update #programs set run = 'Y' where @DatabaseName = Name

-- Count if any more to process. If not, exit

set @DBCount = (select count(*) from #programs where run <> 'Y')

if @DBCount = 0 goto Finished

goto NextDB

Finished:

I also use "forfiles" periodically to delete the backups after "X" weeks.