Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backuping Stored procs of the Database by using scripts Expand / Collapse
Author
Message
Posted Monday, September 30, 2013 12:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, April 12, 2014 6:52 PM
Points: 15, Visits: 58
hi all,

i want to create a script to backup the stored procs of the database plz suggest.
Post #1500157
Posted Monday, September 30, 2013 1:31 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 302, Visits: 1,067
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.
Post #1500179
Posted Monday, September 30, 2013 1:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 11,940, Visits: 10,974
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1500180
Posted Tuesday, October 01, 2013 4:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 14,788, Visits: 27,264
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1500328
Posted Tuesday, October 01, 2013 7:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
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.



Post #1500402
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse