Pull Drop Create job script from sever via Script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pull Drop Create job script from sever via Script

Author

Topic

srussell705
Starting Member

4 Posts

Posted - 2011-05-03 : 14:52:44

I have hundreds of jobs and some change while others stay constant. Add more functionality as a usual case. :(

I want to automate the process of update the job on my warm backup server to be identical to job_id 'D524767A-7A9E-4285-AF7C-AFE7861933CD' on my production one.

Already query for what changed yesterday or within last week. Now how do I get to pull that drop create script and run in on other server? Consider this to become a job to run nightly. 99% of the time nothing to do.

SQL 2005 server

srussell705
Starting Member

4 Posts

Posted - 2011-05-04 : 14:07:15

Did this as the query:
select DISTINCT sysjobs.name as job_name , sysjobs.job_id , SYSCATEGORIES.name AS Cat_Name
FROM msdb..sysjobs
INNER JOIN msdb..syscategories SYSCATEGORIES
ON SYSCATEGORIES.category_id = SYSJOBS.category_id
WHERE SYSCATEGORIES.name <> 'Log Shipping'
ORDER BY 3,1

I generated Categories for better control and filter for all of them except LS being turned on.

Cant figure out how to pull all the enabled ones from server a to b as of yet.

platteman
Starting Member

4 Posts

Posted - 2011-06-29 : 16:05:40

I found-changed this powershell script and it woks for me. Credit goes to someone but unsure where I got the code from. I do know if you have a sql server with a \ in the name if will fail.

#Original

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
# Get List of sql servers to check

#$sqlservers = Get-Content "$Env:USERPROFILE\sqlservers.txt";
$sqlservers = Get-Content "C:\powershell\Servers.txt";

# Loop through each sql server from sqlservers.txt
foreach($sqlserver in $sqlservers)

{
# create folder
New-Item $sqlservers -type directory
# Create an SMO Server object
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;

# Jobs counts
$totalJobCount = $srv.JobServer.Jobs.Count;
$failedCount = 0;
$successCount = 0;

# For each jobs on the server
foreach($job in $srv.JobServer.Jobs)

{
# Default write colour
$colour = "Green";
$jobName = $job.Name;
$jobEnabled = $job.IsEnabled;
$jobLastRunOutcome = $job.LastRunOutcome;
$jobNameFile = "C:\powershell\$sqlserver\" + $jobName+".sql"

Write-Host $job.Name
Write-Host "The location of the file is called " $jobNameFile

# [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
# $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
# #$srv.JobServer.Jobs | foreach {$_.Script()} | out-file -path $path
$job | foreach {$_.Script()} | out-file $jobNameFile

# Set write text to red for Failed jobs
if($jobLastRunOutcome -eq "Failed")

{

$colour = "Red";
$failedCount += 1;
}

elseif ($jobLastRunOutcome -eq "Succeeded")
{
$successCount += 1;
}

Write-Host -ForegroundColor $colour "SERVER = $sqlserver JOB = $jobName ENABLED = $jobEnabled LASTRUN = $jobLastRunOutcome";
}

# Writes a summary for each SQL server
Write-Host -ForegroundColor red "=========================================================================================";
Write-Host -ForegroundColor red "$sqlserver total jobs = $totalJobCOunt, success count $successCount, failed jobs = $failedCount.";
Write-Host -ForegroundColor red "=========================================================================================";
}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources