MS SQL批量生成作业脚本方法介绍总结

在迁移或升级SQL Server数据库服务器时，很多场景下我们不能还原msdb，所以我们必须手工迁移SQL Server相关作业。如果手工生成每一个作业的脚本话，费时又费力，其实SQL Server中有好几种方法可以批量生成作业脚本的方法， 下面介绍一下。

 

1：SSMS客户端工具批量生成创建作业脚本

 

1：在SSMS—>SQL Server Agent下，单击Jobs。

 

2： 按快捷键F7，在Object Explorer Details里面，你会看到所有的作业

 

3： 全选所有作业（CTRL+A）,然后右键单击“Script Job as”，然后选项“CREATE TO"就会生成所有作业的脚本。

 

 

 

这个方法其实是非常简洁方便的。不足之处就是所有作业的脚本位于同一个Script，没有按作业名生成相关对应的脚本。

 

 

2：使用PowerShell脚本生成所有作业的脚本

 

有一个现成的PowerShell脚本,相关介绍以及脚本下载具体参考下面链接：

 

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!

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

 

使用该PowerShell脚本可以生成各个作业的各自脚本。非常简洁方便。不过该脚本要求Windows PowerShell 2.0或更高的版本，另外，有些平台是没有测试过的，需要参考上面链接说明。 GetAllAgentJobs.ps1的脚本如下：

 

<#
 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!'
}

 

 

方法3：通过SqlDmo组件相关函数生成数据库对象脚本，不过有些版本由于没有安装"Backward Compatibility Components" 导致该脚本不能创建相关脚本文件，个人测试时也遇到不少问题，有兴趣可以试试。

 

 

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

 

 

参考资料：

 

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