Hi,
I came across a request on MSDN where one of our colleague was running SQL 2008. He want to create a SQL Agent Job which when executed Script Out All existing SQL Agent Jobs. As per him he don't want to use SMO or VB.
I provided POWERSHELL script to do so.
1) Copy below code in a Text Pad and save it as JOBS.PS1
param($sqlserver)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.JobServer.Jobs foreach {$_.Script()}
2) Create a New Job in SSMS.
3) In Steps add a step of Type as "Powershell".
4) In Command section provide location of file JOBS.PS1 and Server Name from where we want Jobs
Information.
C:\./JOBS.PS1 GURPSETH\SQL2K*
5) Go to Advances section for this Job Step and provide file in OUTPUT FILE section. idea is once this
command will run it should log the output in it.
6) Once done save this job.
7) Starts this job.
