Technical Article

Scripting SQL jobs using PowerShell

,

This powershell script can be used to generate scripts for sql jobs with different filenames.

I have customised this script in such a way that it will not script any "Replication" or "logshipping" category Jobs.

You can modify the script as per your requirement.

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

Step 1) Create a folder in your C drive as "test"

Step 2) Save the script with extension PS1. as "job_script"

Step 3) Pass the "instance name" as the parameter. This will create the scripts for the specified instance name.

Premjit Das

param($sqlserver)

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlserver

$jobs = $srv.JobServer.Jobs | Where-Object {$_.category -notlike "*repl*" -and $_.category -notlike "*shipping*" -and $_.category -notlike "*Maintenance*" } 


ForEach ( $job in $jobs )
    {
        $jobname = "c:\test\" + $job.Name.replace(" ","_").replace("\","_").replace("[","_").replace("]","_").replace(".","_").replace(":","_").replace("*","_") + ".sql"
        $job.Script() | Out-File $jobname
    }

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (2)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (2)

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

SQL Server PowerShell Extensions (SQLPSX) Part 1

Powershell is the new management interface for scripting and working with SQL Server that is integrated into Windows 2008 and SQL Server 2008. The author of a Codeplex project introduces some extensions that try to make it easier to use Powershell with SQL Server.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (16)

You rated this post out of 5. Change rating

2008-10-06

18,273 reads