MS SQL批量生成作业脚本方法介绍总结
2017-07-25 11:21 潇湘隐者 阅读(2206) 评论(0) 编辑 收藏 举报在迁移或升级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

· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
2016-07-25 Linux文件操作常用命令整理
2016-07-25 SQL Server 连接超时案例一则
2014-07-25 SQL Server 中VARCHAR(MAX)变量赋值引起的性能问题。