SQL 作业(Jobs)的创建及应用

1. 如何查看SQLServer的作业信息

--存储特定作业与一个或多个目标服务器的关联或关系。该表存储在 msdb 数据库中。
select * from msdb.dbo.sysjobservers 

--dbo.sysjobschedules包含将由 SQL Server代理执行的作业的计划信息。该表存储在 msdb 数据库中。（计划运行作业的下一个日期）
--注意：Sysjobschedules 表每20分钟刷新一次，这可能会影响 sp_help_jobschedule 存储过程返回的值。
select * from msdb.dbo.sysjobschedules

--dbo.sysjobs存储将由 SQL Server代理执行的各个预定作业的信息。该表存储在 msdb 数据库中。
select * from msdb.dbo.sysjobs

--dbo.sysjobsteps包含 SQL Server代理要执行的作业中的各个步骤的信息。该表存储在 msdb 数据库中。
select * from msdb.dbo.sysjobsteps

--多表结合查看
SELECT  * FROM msdb.dbo.sysjobs a WITH ( nolock )
INNER JOIN msdb.dbo.sysjobsteps b WITH ( nolock ) ON b.job_id = a.job_id

SELECT sj.name,
       sh.run_date,
       sh.step_name,
       STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
       STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS)  '
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sh
ON sj.job_id = sh.job_id

2. 创建作业

2.1 使用 SQL Server Management Studio

(1) 在 “对象资源管理器” (object explorer)中，单击加号以展开要创建 SQL Server 代理作业的服务器
(2) 单击加号以展开 “SQL Server 代理”(SQL Server Agent)。
(3) 右键单击“作业”(Jobs)文件夹，然后选择“新建作业…”(New Job…)。
(4) 在 “新建作业” 对话框的 “常规” (General)页上，修改作业的常规属性，比如输入作业名称、作业所有者等信息
(5) 在 “步骤” (Steps)页上，组织作业步骤。
(6) 在 “计划”(Schedules) 页上，组织作业的计划。
(7) 在 “警报” (Alerts)页上，组织作业的警报。
(8) 在“通知”(Nortifications)页上，设置在作业完成时 MicrosoftMicrosoft SQL ServerSQL Server 代理要执行的操作。
(9) 在 “目标” (Targets)页上，管理作业的目标服务器。
(10) 完成后，单击 “确定” 。

2.2 使用 T-SQL

USE msdb ;  
GO  
EXEC dbo.sp_add_job  
    @job_name = N'Weekly Sales Data Backup' ;  
GO  
EXEC sp_add_jobstep  
    @job_name = N'Weekly Sales Data Backup',  
    @step_name = N'Set database to read only',  
    @subsystem = N'TSQL',  
    @command = N'ALTER DATABASE SALES SET READ_ONLY',   
    @retry_attempts = 5,  
    @retry_interval = 5 ;  
GO  
EXEC dbo.sp_add_schedule  
    @schedule_name = N'RunOnce',  
    @freq_type = 1,  
    @active_start_time = 233000 ;  
USE msdb ;  
GO  
EXEC sp_attach_schedule  
   @job_name = N'Weekly Sales Data Backup',  
   @schedule_name = N'RunOnce';  
GO  
EXEC dbo.sp_add_jobserver  
    @job_name = N'Weekly Sales Data Backup';  
GO

3. 实例应用

3.1 每周一早上执行指定路径下的脚本，并将执行日志导出成txt文件

declare @text nvarchar(800)=  'sqlcmd -E -i \\XXX.XX.XX.XXX\XXX.rpt';