个人中心
文章发布
退出
作者热门文章
- html - 出于某种原因，IE8 对我的 Sass 文件中继承的 html5 CSS 不友好？
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景？
26
4
在 SQL Server Management Studio 中，我可以右键单击作业，将作业编写为，然后将创建的脚本保存为 .sql 文件。为了支持我的工作，我想自动化上述过程，但找不到任何方法来做到这一点。这可能吗？怎么办？
最佳答案
除了备份存储它们的 msdb 数据库之外，您还可以将它们编写一次脚本，然后每天检查是否有更改。如果没有更改，则没有任何理由再次备份它们。如果您确实发现有人更改了工作或日程安排，我确信您下一个问题将是实际更改了什么以及何时更改的。否则，您将不知道要恢复到哪个版本的作业。
我不久前写了一个程序来做到这一点。您可以在 GitHub 上找到它因为这里发帖太长了。该脚本的一个依赖项是它将当前作业、计划等记录到名为AdminTools 的数据库中的表中。这样我们就可以将 msdb 中的内容与我们记录的内容进行比较。您可以更改此设置以使用您想要放置日志记录表的任何数据库。
此外，我还编写了一个脚本来为代理警报执行相同的操作。也可以在 GitHub 上找到。 .
这是代理作业过程的内容，它提取所有作业和计划，并以与 SSMS 非常相似的方式显示它(因此 case 中的所有逻辑声明)。
select
jobs.job_id
,job_name = jobs.name
,job_desc = jobs.description
,jobs.enabled
,jobs.date_created
,jobs.date_modified
,jobs.version_number
,jobs.start_step_id --ID of the step in the job where execution should begin
,job_owner = serv_princ.name --Security identifier number (SID) of the job owner, to check if job now owned by SA --need to work on this
,notify_level_eventlog = case
when jobs.notify_level_eventlog = 0 then 'Never'
when jobs.notify_level_eventlog = 1 then 'When the job succeeds'
when jobs.notify_level_eventlog = 2 then 'When the job fails'
when jobs.notify_level_eventlog = 3 then 'When the job completes (regardless of outcome)'
end
,notify_level_email = case
when jobs.notify_level_email = 0 then 'Never'
when jobs.notify_level_email = 1 then 'When the job succeeds'
when jobs.notify_level_email = 2 then 'When the job fails'
when jobs.notify_level_email = 3 then 'When the job completes (regardless of outcome)'
end
,email_operator_name = emailop.name
,email_operator_email = emailop.email_address
,email_operator_enabled = emailop.enabled
,level_page = case
when jobs.notify_level_page = 0 then 'Never'
when jobs.notify_level_page = 1 then 'When the job succeeds'
when jobs.notify_level_page = 2 then 'When the job fails'
when jobs.notify_level_page = 3 then 'When the job completes (regardless of outcome)'
end
,page_operator_name = pageop.name
,page_operator_address = pageop.pager_address
,page_operator_enabled = pageop.enabled
,page_operator_scheduled_days = SUBSTRING(
CASE WHEN pageop.pager_days & 1 = 1 THEN ',Sun' ELSE '' END
+ CASE WHEN pageop.pager_days & 2 = 2 THEN ',Mon' ELSE '' END
+ CASE WHEN pageop.pager_days & 4 = 4 THEN ',Tues' ELSE '' END
+ CASE WHEN pageop.pager_days & 8 = 8 THEN ',Wed' ELSE '' END
+ CASE WHEN pageop.pager_days & 16 = 16 THEN ',Thurs' ELSE '' END
+ CASE WHEN pageop.pager_days & 32 = 32 THEN ',Fri' ELSE '' END
+ CASE WHEN pageop.pager_days & 64 = 64 THEN ',Sat' ELSE '' END
, 2, 64)
,page_operator_weekday_sked = stuff(stuff(right('00000' + cast(pageop.weekday_pager_start_time as varchar),6),3,0,':'),6,0,':') + ' - ' + stuff(stuff(right('00000' + cast(pageop.weekday_pager_end_time as varchar),6),3,0,':'),6,0,':')
,page_operator_saturday_sked = stuff(stuff(right('00000' + cast(pageop.saturday_pager_start_time as varchar),6),3,0,':'),6,0,':') + ' - ' + stuff(stuff(right('00000' + cast(pageop.saturday_pager_end_time as varchar),6),3,0,':'),6,0,':')
,page_operator_sunday_sked = stuff(stuff(right('00000' + cast(pageop.sunday_pager_start_time as varchar),6),3,0,':'),6,0,':') + ' - ' + stuff(stuff(right('00000' + cast(pageop.sunday_pager_end_time as varchar),6),3,0,':'),6,0,':')
,steps.step_id
,steps.step_name
,steps.command
,on_success_action = case
when steps.on_success_action = 1 then 'Quit reporting success'
when steps.on_success_action = 2 then 'Quit reporting failure'
when steps.on_success_action = 3 then 'Go to next step (' + cast(steps.step_id + 1 as varchar) + ')'
when steps.on_success_action = 4 then 'Go to step: ' + cast(on_fail_step_id as varchar)
end
,on_fail_action = case
when steps.on_fail_action = 1 then 'Quit reporting success'
when steps.on_fail_action = 2 then 'Quit reporting failure'
when steps.on_fail_action = 3 then 'Go to next step (' + cast(steps.step_id + 1 as varchar) + ')'
when steps.on_fail_action = 4 then 'Go to step: ' + cast(on_fail_step_id as varchar)
end
,last_run_outcome = case
when steps.last_run_outcome = 0 then 'Failed'
when steps.last_run_outcome = 1 then 'Succeeded'
when steps.last_run_outcome = 2 then 'Retry'
when steps.last_run_outcome = 3 then 'Canceled'
when steps.last_run_outcome = 5 then 'Unknown'
else 'Undefined'
end
,last_run_date = case when steps.last_run_date = 0 then 'Never' else stuff(stuff(steps.last_run_date,5,0,'-'),8,0,'-') end
,last_run_time = stuff(stuff(right('00000' + cast(steps.last_run_time as varchar),6),3,0,':'),6,0,':')
,last_run_duration = stuff(stuff(right('00000' + cast(steps.last_run_duration as varchar),6),3,0,':'),6,0,':')
from
msdb.dbo.sysjobs jobs
left join
msdb.dbo.sysjobsteps steps on
steps.job_id = jobs.job_id
left join
msdb.dbo.sysoperators emailop on
emailop.id = jobs.notify_email_operator_id
left join
msdb.dbo.sysoperators pageop on
pageop.id = jobs.notify_email_operator_id
left join
sys.server_principals serv_princ on
serv_princ.sid = jobs.owner_sid
select
sched.schedule_uid
,sched.schedule_id
,job_name = jobs.name
,job_enabled = jobs.enabled
,schedule_name = sched.name
,schedule_frequency = case
when sched.freq_type = 1 then 'One time only on '
+ stuff(stuff(sched.active_start_date,5,0,'-'),8,0,'-')
+ ' at '
+ case when sched.active_start_time = 0 then '00:00:00' else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':') end
when sched.freq_type = 4 then 'Every '
+ cast(sched.freq_interval as varchar)
+ ' days,'
+ case
when sched.freq_subday_type = 1 then ' at '
when sched.freq_subday_type = 2 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' seconds, from '
when sched.freq_subday_type = 4 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' minutes, from '
when sched.freq_subday_type = 8 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' hours, from '
end
+ case
when sched.freq_subday_type = 1 then case
when sched.active_start_time = 0 then '00:00:00'
else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':')
end
else
case when sched.active_start_time = 0 then '00:00:00' else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':') end
+ ' - '
+ case when sched.active_end_time = 0 then '00:00:00'else stuff(stuff(sched.active_end_time,3,0,':'),6,0,':') end
end
+ ' beginning '
+ stuff(stuff(sched.active_start_date,5,0,'-'),8,0,'-')
+ ' and ending '
+ case when sched.active_end_date = 99991231 then 'Never' else stuff(stuff(sched.active_end_date,5,0,'-'),8,0,'-') end
when sched.freq_type = 8 then 'Every '
+ cast(sched.freq_recurrence_factor as varchar)
+ ' weeks on '
+ SUBSTRING(
CASE WHEN sched.freq_interval & 1 = 1 THEN ',Sun' ELSE '' END
+ CASE WHEN sched.freq_interval & 2 = 2 THEN ',Mon' ELSE '' END
+ CASE WHEN sched.freq_interval & 4 = 4 THEN ',Tues' ELSE '' END
+ CASE WHEN sched.freq_interval & 8 = 8 THEN ',Wed' ELSE '' END
+ CASE WHEN sched.freq_interval & 16 = 16 THEN ',Thurs' ELSE '' END
+ CASE WHEN sched.freq_interval & 32 = 32 THEN ',Fri' ELSE '' END
+ CASE WHEN sched.freq_interval & 64 = 64 THEN ',Sat' ELSE '' END
, 2, 64)
+ case
when sched.freq_subday_type = 1 then ' at '
when sched.freq_subday_type = 2 then ', every ' + cast(sched.freq_subday_interval as varchar) + ' seconds, from '
when sched.freq_subday_type = 4 then ', every ' + cast(sched.freq_subday_interval as varchar) + ' minutes, from '
when sched.freq_subday_type = 8 then ', every ' + cast(sched.freq_subday_interval as varchar) + ' hours, from '
end
+ case
when sched.freq_subday_type = 1 then case
when sched.active_start_time = 0 then '00:00:00'
else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':')
end
else
case when sched.active_start_time = 0 then '00:00:00' else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':') end
+ ' - '
+ case when sched.active_end_time = 0 then '00:00:00'else stuff(stuff(sched.active_end_time,3,0,':'),6,0,':') end
end
+ ' beginning '
+ stuff(stuff(sched.active_start_date,5,0,'-'),8,0,'-')
+ ' and ending '
+ case when sched.active_end_date = 99991231 then 'Never' else stuff(stuff(sched.active_end_date,5,0,'-'),8,0,'-') end
when sched.freq_type = 16 then 'Every '
+ cast(sched.freq_recurrence_factor as varchar)
+ ' months, on day '
+ cast(sched.freq_interval as varchar)
+ ' of that month,'
+ case
when sched.freq_subday_type = 1 then ' at '
when sched.freq_subday_type = 2 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' seconds, from '
when sched.freq_subday_type = 4 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' minutes, from '
when sched.freq_subday_type = 8 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' hours, from '
end
+ case
when sched.freq_subday_type = 1 then case
when sched.active_start_time = 0 then '00:00:00'
else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':')
end
else
case when sched.active_start_time = 0 then '00:00:00' else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':') end
+ ' - '
+ case when sched.active_end_time = 0 then '00:00:00'else stuff(stuff(sched.active_end_time,3,0,':'),6,0,':') end
end
+ ' beginning '
+ stuff(stuff(sched.active_start_date,5,0,'-'),8,0,'-')
+ ' and ending '
+ case when sched.active_end_date = 99991231 then 'Never' else stuff(stuff(sched.active_end_date,5,0,'-'),8,0,'-') end
when sched.freq_type = 32 then 'Every '
+ case
when sched.freq_relative_interval = 1 then 'first '
when sched.freq_relative_interval = 2 then 'second '
when sched.freq_relative_interval = 4 then 'third '
when sched.freq_relative_interval = 8 then 'fourth '
when sched.freq_relative_interval = 16 then 'last '
else ''
end
+ case
when sched.freq_interval = 1 then 'Sunday'
when sched.freq_interval = 2 then 'Monday'
when sched.freq_interval = 3 then 'Tuesday'
when sched.freq_interval = 4 then 'Wednesday'
when sched.freq_interval = 5 then 'Thursday'
when sched.freq_interval = 6 then 'Friday'
when sched.freq_interval = 7 then 'Saturday'
when sched.freq_interval = 8 then 'day'
when sched.freq_interval = 9 then 'weekday'
when sched.freq_interval = 10 then 'weekend'
end
+ ', of every '
+ cast(sched.freq_recurrence_factor as varchar)
+ ' months,'
+ case
when sched.freq_subday_type = 1 then ' at '
when sched.freq_subday_type = 2 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' seconds, from '
when sched.freq_subday_type = 4 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' minutes, from '
when sched.freq_subday_type = 8 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' hours, from '
end
+ case
when sched.freq_subday_type = 1 then case
when sched.active_start_time = 0 then '00:00:00'
else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':')
end
else
case when sched.active_start_time = 0 then '00:00:00' else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':') end
+ ' - '
+ case when sched.active_end_time = 0 then '00:00:00'else stuff(stuff(sched.active_end_time,3,0,':'),6,0,':') end
end
+ ' beginning '
+ stuff(stuff(sched.active_start_date,5,0,'-'),8,0,'-')
+ ' and ending '
+ case when sched.active_end_date = 99991231 then 'Never' else stuff(stuff(sched.active_end_date,5,0,'-'),8,0,'-') end
when sched.freq_type = 64 then 'When SQL Server Agent service starts beginning '
+ stuff(stuff(sched.active_start_date,5,0,'-'),8,0,'-')
+ ' and ending '
+ case when sched.active_end_date = 99991231 then 'Never' else stuff(stuff(sched.active_end_date,5,0,'-'),8,0,'-') end
when sched.freq_type = 128 then 'When computer is idle beginning '
+ stuff(stuff(sched.active_start_date,5,0,'-'),8,0,'-')
+ ' and ending '
+ case when sched.active_end_date = 99991231 then 'Never' else stuff(stuff(sched.active_end_date,5,0,'-'),8,0,'-') end
end
,next_run_date = case
when sched.freq_type = 64 and sched.enabled = 1 then 'When SQL Server Agent service starts'
when sched.freq_type = 128 and sched.enabled = 1 then 'When computer is idle'
else
case when jobsched.next_run_date = 0 then 'Never' else stuff(stuff(jobsched.next_run_date,5,0,'-'),8,0,'-') end
end
,next_run_time = case
when sched.freq_type = 64 and sched.enabled = 1 then 'When SQL Server Agent service starts'
when sched.freq_type = 128 and sched.enabled = 1 then 'When computer is idle'
else
case when jobsched.next_run_date = 0 then 'Never' else case when jobsched.next_run_time = 0 then '00:00:00' else stuff(stuff(jobsched.next_run_time,3,0,':'),6,0,':') end end
end
,schedule_enabled = sched.enabled
,sched.version_number
,sched.date_created
,sched.date_modified
from
msdb.dbo.sysschedules sched
inner join
msdb.dbo.sysjobschedules jobsched on
jobsched.schedule_id = sched.schedule_id
inner join
msdb.dbo.sysjobs jobs on
jobs.job_id = jobsched.job_id
关于sql-server - SQL Server 自动创建作业脚本，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/52989962/
26
4
0
SQL、PL-SQL 和 T-SQL 之间有什么区别？ 谁能解释一下这三者之间的区别，并提供每一个的相关使用场景？ 最佳答案 SQL 是一种对集合进行操作的查询语言。 它或多或少是标准化的，几乎所有关
这个问题已经有答案了: What is the difference between SQL, PL-SQL and T-SQL? (6 个回答) 已关闭 9 年前。 我对 SQL 的了解足以完成我的
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列，该列有一个默认约束为 GETDATE()。 使用 LINQ 时，我没有为 RegistrationDate 列提供任
我有一个可能属于以下类型的字符串 string expected result 15-th-rp 15 15/12-rp 12 15-12-th
很难说出这里问的是什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或言辞激烈，无法以目前的形式合理回答。如需帮助澄清此问题以便可以重新打开，visit the help center . 9年前关闭
我有一个存储过程(称为 sprocGetArticles)，它从文章表中返回文章列表。这个存储过程没有任何参数。 用户可以对每篇文章发表评论，我将这些评论存储在由文章 ID 链接的评论表中。 有什么方
我目前正在做一个 *cough*Oracle*cough* 数据库主题。讲师介绍embedded SQL作为让其他语言(例如 C、C++)与(Oracle)数据库交互的方式。 我自己做了一些数据库工作
SQL Server 中 SQL 语句的最大长度是多少？这个长度是否取决于 SQL Server 的版本？ 例如，在 DECLARE @SQLStatement NVARCHAR(MAX) = N'S
这个问题已经有答案了: Simple way to transpose columns and rows in SQL? (9 个回答) 已关闭 8 年前。 CallType
预先感谢您对此提供的任何帮助。 假设我有一个查询，可以比较跨年的数据，从某个任意年份开始，永无止境(进入 future )，每年同一时期直到最后一个完整的月份(其特点是一月数据永远不会显示至 2 月
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列，该列的默认约束为 GETDATE()。 使用 LINQ 时，我没有为 RegistrationDate 列提供任何数
下面是我试图用来检查存储过程是否不存在然后创建过程的 sql。它会抛出一个错误:Incorrect syntax near the keyword 'PROCEDURE' IF NOT EXISTS
我有一个同事声称动态 SQL 在许多情况下比静态 SQL 执行得更快，所以我经常看到 DSQL 到处都是。除了明显的缺点，比如在运行之前无法检测到错误并且更难阅读，这是否准确？当我问他为什么一直使用
来自 lobodava 的动态 SQL 查询是: declare @sql nvarchar(4000) = N';with cteColumnts (ORDINAL_POSITION, CO
使用 SQL Server 中的存储过程执行动态 SQL 命令的现实优点和缺点是什么 EXEC (@SQL) 对比 EXEC SP_EXECUTESQL @SQL ？ 最佳答案 sp_executes
我有这个有效的 SQL 查询: select sum(dbos.Points) as Points, dboseasons.Year from dbo.StatLines dbos i
我正在调试一些构建成功运行的 SQL 命令的代码。 然而，在查询结束时，查询结果似乎被写入了一个文本文件。 完整的查询如下 echo SELECT DATE,DATETABLE,DATE,APPDAT
我有一些创建表的 .sql 文件(MS SQL 数据库): 表_1.sql: IF OBJECT_ID (N'my_schema.table1', N'U') IS NOT NULL DROP TAB
我写了下面的 SQL 存储过程，它一直给我错误@pid = SELECT MAX(... 整个过程是: Alter PROCEDURE insert_partyco @pname varchar(20
我在 SQL Server 2005 中有包含两列 Fruit 和 Color 的表，如下所示 Fruit Colour Apple Red Orange
我是一名优秀的程序员,十分优秀！