I am busy making a backup of all Agent Jobs by scripting them all to file.`How would i rewrite the below to use BCP instead
BEGIN
SET @EchoCommand = 'ECHO ' + @EchoField + ' >> ' + @Path
+ @JobName
+ REPLACE(REPLACE(CONVERT(VARCHAR(200), GETDATE()), ' ',
''), ':', '') + '.sql'
Something similar to the below
SET @EchoCommand = 'bcp
"exec['+DB_NAME()+'].dbo.CreateBackUpFromJob' +
@JobName + '" queryout "\\ServerName\SQL_Backups\' + @JobName +
'.sql" -c -UTF8 -T -SLOCALSERVER'
It's very easy to script out SQL Server Agent Jobs using Powershell
(if that is an option). The following is an example of how you can use Powershell
to script the Agent Jobs and also delete previous scripted jobs that were created over a number of days ago. This script generates a file per Agent Job
You can play around with the code to see if it meets your needs.
# Set sql server
$Instances = "localhost"
foreach($Instance in $Instances)
{
# Create sql server and db objects
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $Instance;
# Write out the job names
#$srv.JobServer.jobs|foreach-Object {Write-Host "$_.name" }
# Script out the Jobs
$CurrentDate = get-date -f "yyyy-MM-dd"
$Instance = $Instance.Replace("\","-") + "-" + $CurrentDate
$srv.JobServer.jobs|foreach-Object {$_.script() > "C:\Temp\SqlJobs\$Instance-$_.txt"}
}
# Purge files older than 2 weeks
$cutoff = (get-date).AddDays(-14)
dir "C:\Temp\SqlJobs\"|Get-ChildItem -incl '*.txt' -rec|Where{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer}|remove-item