Table of Contents



Problem


While migrating a sql server database to another sql server instance, sometimes we want to move sql server jobs also.

Solution


I am using the following powershell script to produce script of the sql server jobs and using it on the new sql server instance

1.[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
2.  
3.$server = New-Object Microsoft.SqlServer.Management.Smo.Server("localhost"
4.$scripter = New-Object Microsoft.SqlServer.Management.Smo.Scripter($server)
5.$jobs = $server.JobServer.get_Jobs() | Where-Object {$_.Name -notlike "sys*"}    
6.$script = ""
7.foreach($job in $jobs){ $script += $job.Script() + "GO`n" }
8.$script >> "c:\sqljobs.sql"