SP Help

Question

SP Help

rocky_498

SSCertifiable

Points: 6532
More actions
February 1, 2014 at 9:51 pm

#396206

Hi Guys,
I want to know, is there a way I can BackUp all Store Procedure in .txt? I know how to do manually app SP but I want to schedule
a job everyday Backup all SP and create new .txt file. Any help would be great appreciate.
Thank You.

Viewing 3 posts - 1 through 2 (of 2 total)

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

swansink 96600 Newbie Points: 8 More actions · Answer 1

I wish i could remember and credit the original source of this, but i have used this powershell to generate txt versions of my objects.

function global:Script-DBObjectsIntoFolders([string]$server, [string]$dbname){

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $server

$db = $SMOserver.databases[$dbname]

$Objects = $db.Tables

$Objects += $db.Views

$Objects += $db.StoredProcedures

$Objects += $db.UserDefinedFunctions

#Build this portion of the directory structure out here in case scripting takes more than one minute.

$SavePath = "C:\TEMP\Databases\" + $($dbname)

$DateFolder = get-date -format yyyyMMddHHmm

new-item -type directory -name "$DateFolder"-path "$SavePath"

foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject)}) {

#Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name

$scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)

$scriptr.Options.AppendToFile = $True

$scriptr.Options.AllowSystemObjects = $False

$scriptr.Options.ClusteredIndexes = $True

$scriptr.Options.DriAll = $True

$scriptr.Options.ScriptDrops = $False

$scriptr.Options.IncludeHeaders = $True

$scriptr.Options.ToFileOnly = $True

$scriptr.Options.Indexes = $True

$scriptr.Options.Permissions = $True

$scriptr.Options.WithDependencies = $False

<#Script the Drop too#>

$ScriptDrop = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)

$ScriptDrop.Options.AppendToFile = $True

$ScriptDrop.Options.AllowSystemObjects = $False

$ScriptDrop.Options.ClusteredIndexes = $True

$ScriptDrop.Options.DriAll = $True

$ScriptDrop.Options.ScriptDrops = $True

$ScriptDrop.Options.IncludeHeaders = $True

$ScriptDrop.Options.ToFileOnly = $True

$ScriptDrop.Options.Indexes = $True

$ScriptDrop.Options.WithDependencies = $False

<#This section builds folder structures. Remove the date folder if you want to overwrite#>

$TypeFolder=$ScriptThis.GetType().Name

if ((Test-Path -Path "$SavePath\$DateFolder\$TypeFolder") -eq "true")

{"Scripting Out $TypeFolder $ScriptThis"}

else {new-item -type directory -name "$TypeFolder"-path "$SavePath\$DateFolder"}

$ScriptFile = $ScriptThis -replace "\[|\]"

$ScriptDrop.Options.FileName = "" + $($SavePath) + "\" + $($DateFolder) + "\" + $($TypeFolder) + "\" + $($ScriptFile) + ".SQL"

$scriptr.Options.FileName = "$SavePath\$DateFolder\$TypeFolder\$ScriptFile.SQL"

#This is where each object actually gets scripted one at a time.

$ScriptDrop.Script($ScriptThis)

$scriptr.Script($ScriptThis)

} #This ends the loop

} #This completes the function

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

Below is a T-SQL script I created last month for this kind of thing. Note my comments, you will have to make a couple changes for this to work.

1) You will have to replace SOMEDB with the name of the DB you want to use.

2) You will have to change YOURSERVER & YOURSHARE to the server/folder that you want to output the txt file too.

Let us know if this helps!

-- variable that will contain the commands to rebuild the sprocs

-- change the USE statement to your target DB

DECLARE @rebuild_sprocs_script nvarchar(max)=

N'-- Let''s build some stored procs....'+CHAR(10)+

N'USE SOMEDB;'+CHAR(10)+'GO '+CHAR(10)+

REPLICATE('-',100)+CHAR(10)+CHAR(10);

-- global temp table that can be called from a Dynamic SQL statement

IF OBJECT_ID('tempdb..##sprocScript') IS NOT NULL DROP TABLE ##sprocScript;

CREATE TABLE ##sprocScript (cmd nvarchar(max));

-- Capture the routine definitions for all user stored procedures

WITH SPROCS AS

(

SELECTROW_NUMBER() OVER (ORDER BY ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME) rn,

ROUTINE_CATALOG,

ROUTINE_SCHEMA+'.['+ROUTINE_NAME+']' AS NAME,

ISNULL(OBJECT_DEFINITION(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTINE_NAME)),

OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_NAME)))

AS ROUTINE_DEFINITION_COMPLETE

FROM information_schema.routines

WHERE ROUTINE_BODY<>'EXTERNAL'

AND ROUTINE_TYPE='PROCEDURE'

AND LEFT(ROUTINE_NAME,3)<>'dbo'

)

SELECT @rebuild_sprocs_script+=

CHAR(10)+'--'+NAME+CHAR(10)+CHAR(10)+

'IF OBJECT_ID(''SOMEDB.'+NAME+''') IS NOT NULL DROP PROC '+NAME+CHAR(10)+'GO'+CHAR(10)+

ROUTINE_DEFINITION_COMPLETE+CHAR(10)+'GO '+CHAR(10)+REPLICATE('-',100)

FROM SPROCS

ORDER BY rn;

--insert the commands to drop/re-create the sprocs into the global temp table

INSERT INTO ##sprocScript

SELECT @rebuild_sprocs_script;

GO

--uncomment to see the output

--SELECT * FROM ##sprocScript

--Create the SQL file

--change the location

EXEC xp_cmdshell 'bcp "SELECT TOP 1 cmd FROM ##sprocScript" queryout "\\YOURSERVER\YOURSHARE\sproc_rebuild.txt" -T -c -t,'

GO

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001