When migrating or upgrading SQL Server database servers, we cannot restore msdb in many scenarios, so we have to migrate SQL Server related jobs manually. If it is time-consuming and laborious to manually generate scripts for each job, there are actually several methods in SQL Server that can generate job scripts in batches. Here are some of them.

 

1: SSMS Client Tool Batch Generation Create Job Script

 

1: Under SMS - > SQL Server Agent, click Jobs.

 

2: Press F7, in Object Explorer Details, you will see all the jobs

 

3: Select all jobs (CTRL+A), right-click "Script Job as" and then choose "CREATE TO" to generate scripts for all jobs.

 

 

 

This method is actually very simple and convenient. The disadvantage is that scripts for all jobs are located in the same Script, and no corresponding scripts are generated by job name.

 

 

2: Use PowerShell scripts to generate scripts for all jobs

 

There is a ready-made PowerShell script, related introduction and script download specific reference to the following links:

 

https://gallery.technet.microsoft.com/scriptcenter/How-to-get-the-of-all-the-81859696

 

 

PS C:\Users> M:\GetJobScripts\GetAllAgentJobs.ps1

 

cmdlet GetAllAgentJobs.ps1 at command pipeline position 1

Supply values for the following parameters:

ServerName: YourServerName

FilePath: M:\GetJobScripts

Scripting out  xxxxxxxxxxxx  successfully!

Scripting out  xxxxxxxxxxxx  successfully!

.........................................

 

The PowerShell script can be used to generate individual scripts for each job. Very simple and convenient. However, the script requires Windows PowerShell version 2.0 or higher, and in addition, some platforms have not been tested, you need to refer to the link above. The script for GetAllAgentJobs.ps1 is as follows:

 

<#

 The sample scripts are not supported under any Microsoft standard support 

 program or service. The sample scripts are provided AS IS without warranty  

 of any kind. Microsoft further disclaims all implied warranties including,  

 without limitation, any implied warranties of merchantability or of fitness for 

 a particular purpose. The entire risk arising out of the use or performance of  

 the sample scripts and documentation remains with you. In no event shall 

 Microsoft, its authors, or anyone else involved in the creation, production, or 

 delivery of the scripts be liable for any damages whatsoever (including, 

 without limitation, damages for loss of business profits, business interruption, 

 loss of business information, or other pecuniary loss) arising out of the use 

 of or inability to use the sample scripts or documentation, even if Microsoft 

 has been advised of the possibility of such damages. 

#>

Param(

[Parameter(Mandatory = $true, position = 0)][string] $ServerName,

[Parameter(Mandatory = $true, position = 1)][string] $FilePath

)

if(Test-Path $FilePath)

{

        #check if the instance name is available on the server

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

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

         if($ServerName.contains($env:COMPUTERNAME) -and ($srv.VersionString))

         {

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

            ForEach ( $job in $jobs) 

            { 

               $jobname = $FilePath +'\' + $job.Name.replace(" ","_").replace("\","_").replace("[","_").replace("]","_").replace(".","_").replace(":","_").replace("*","_") + ".sql" 

              $job.Script() | Out-File $jobname 

                Write-Host 'Scripting out ' $job ' successfully!'

            }

         }

        else 

        {

        Write-Host 'The server name you entered is not available!'

        }

}

else

{

Write-Host 'The path does not exist, please retype again!'

}

 

 

Method 3: Generate database object script by SqlDmo component related function, but some versions can't create script file because of not installing "Backward Compatibility Components". There are also many problems in personal testing. If you are interested, you can try it.

 

 

http://www.databasejournal.com/features/mssql/article.php/2205291/Generate-Scripts-for-SQL-Server-Objects.htm

 

 

Reference material:

 

https://stackoverflow.com/questions/3361163/automatically-create-scripts-for-all-sql-server-jobs

https://gallery.technet.microsoft.com/scriptcenter/How-to-get-the-of-all-the-81859696

http://www.databasejournal.com/features/mssql/article.php/2205291/Generate-Scripts-for-SQL-Server-Objects.htm