September 30, 2013 at 12:34 pm
hi all,
i want to create a script to backup the stored procs of the database plz suggest.
September 30, 2013 at 1:31 pm
raj.prabhu001 (9/30/2013)
hi all,i want to create a script to backup the stored procs of the database plz suggest.
If this is a one time deal, you can use Management Studio and script those out. Just right click the database, then Task, then Generate Scripts, then select the objects, on this case, store procedures. Then save locally or decide if you want a script per object or a single master script.
September 30, 2013 at 1:34 pm
raj.prabhu001 (9/30/2013)
hi all,i want to create a script to backup the stored procs of the database plz suggest.
Do you not already backup the database??? All the procs will be in the backup too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 1, 2013 at 4:01 am
Another mechanism is to use PowerShell. You can pretty easily walk the structure and generate scripts. Allen White has an example where he does it for the entire database. It'd be pretty easy to pare that down to just stored procedures.
"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
October 1, 2013 at 7:30 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply