SQL SERVER数据库的作业的脚本及存储过程

本站文章旨在为该问题提供解决思路及关键性代码，并不能完成应该由网友自己完成的所有工作，请网友在仔细看文章并理解思路的基础上举一反三、灵活运用

1. IF EXISTS (SELECT name 
2. FROM sysobjects  
3. WHERE name = N'cg_DoBackupJob' 
4. AND type = 'P') 
5. DROP PROCEDURE cg_DoBackupJob 
6. GO  
7.  
8.  
9. CREATE PROCEDURE [cg_DoBackupJob] 
10. @DataBaseName varchar(100), 
11. @FileHead varchar(50), 
12. @isFullBackup bit, -- 0 差量备份 1 完整备份 
13. @FolderPath varchar(50) = 'f:/db_backup/', 
14. @BackName varchar(100) = 'unknown', -- 描述字串 
15. @isAppendMedia bit = 1 -- 0 覆盖媒体 1 追加到媒体  
16.  
17. AS 
18. declare @filePath varchar(150) 
19. declare @sql varchar(1000) 
20.  
21.  
22. select @filePath=@FolderPath + @FileHead + '_' + case @isFullBackup when 1 then 'FullBackup' when 0 then 'DifferBackup' end + '_' + convert ( nvarchar(11) ,getdate() , 112 )  
23. + case @isFullBackup when 1 then '' when 0 then replace(convert(nvarchar(15),getdate(),114),':','') end 
24. --print(@filePath) 
25.  
26. select @sql ='BACKUP DATABASE [' + @DataBaseName + '] TO DISK = '''  
27. + @filePath + ''' WITH ' 
28. + case @isAppendMedia when 0 then 'INIT' when 1 then 'NOINIT' end  
29. + ' , NOUNLOAD , ' 
30. + case @isFullBackup when 0 then 'DIFFERENTIAL , ' when 1 then '' end  
31. + ' NAME = N''' + @BackName + '备份'', NOSKIP , STATS = 10, NOFORMAT' 
32.  
33. execute(@sql) 
34. --print(@sql) 
35. GO 
36.  
37. -- ============================================= 
38. -- example to execute the store procedure 
39. -- ============================================= 
40. EXECUTE cg_DoBackupJob 'cg_access911','access911',1 
41. GO 

用系统存储过程去创建作业，代码如下：

1. BEGIN TRANSACTION 
2. DECLARE @JobID BINARY(16)  
3. DECLARE @ReturnCode INT 
4. SELECT @ReturnCode = 0  
5. IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1  
6. EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]' 
7.  
8.  
9. -- 删除同名的警报（如果有的话）。 
10. SELECT @JobID = job_id  
11. FROM msdb.dbo.sysjobs  
12. WHERE (name = N'access911_每2周备份一次')  
13. IF (@JobID IS NOT NULL)  
14. BEGIN 
15. -- 检查此作业是否为多重服务器作业  
16. IF (EXISTS (SELECT *  
17. FROM msdb.dbo.sysjobservers  
18. WHERE (job_id = @JobID) AND (server_id <> 0)))  
19. BEGIN 
20. -- 已经存在，因而终止脚本  
21. RAISERROR (N'无法导入作业“access911_每2周备份一次”，因为已经有相同名称的多重服务器作业。', 16, 1)  
22. GOTO QuitWithRollback  
23. END 
24. ELSE 
25. -- 删除［本地］作业  
26. EXECUTE msdb.dbo.sp_delete_job @job_name = N'access911_每2周备份一次' 
27. SELECT @JobID = NULL 
28. END 
29.  
30. BEGIN 
31.  
32. -- 添加作业 
33. EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'access911_每2周备份一次', @owner_login_name = N'Access911/access911', @description = N'没有可用的描述。', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 
34. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
35.  
36. -- 添加作业步骤 
37. EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'2周备份', @command = N'EXECUTE cg_DoBackupJob ''a9SupperDatabase'',''a9SupperDatabase'',1 
38. ', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2 
39. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
40. EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1  
41.  
42. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
43.  
44. -- 添加作业调度 
45. EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'diaodu', @enabled = 1, @freq_type = 8, @active_start_date = 20061009, @active_start_time = 0, @freq_interval = 64, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 2, @active_end_date = 99991231, @active_end_time = 235959 
46. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
47.  
48. -- 添加目标服务器 
49. EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
50. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
51.  
52. END 
53. COMMIT TRANSACTION 
54. GOTO EndSave  
55. QuitWithRollback: 
56. IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
57. EndSave:  

Transact-SQL 参考

1. sp_add_jobschedule 
2. 创建作业调度。 
3.  
4. 语法 
5. sp_add_jobschedule [ @job_id = ] job_id, | [ @job_name = ] 'job_name', 
6. [ @name = ] 'name' 
7. [ , [ @enabled = ] enabled ] 
8. [ , [ @freq_type = ] freq_type ] 
9. [ , [ @freq_interval = ] freq_interval ] 
10. [ , [ @freq_subday_type = ] freq_subday_type ] 
11. [ , [ @freq_subday_interval = ] freq_subday_interval ] 
12. [ , [ @freq_relative_interval = ] freq_relative_interval ] 
13. [ , [ @freq_recurrence_factor = ] freq_recurrence_factor ] 
14. [ , [ @active_start_date = ] active_start_date ] 
15. [ , [ @active_end_date = ] active_end_date ] 
16. [ , [ @active_start_time = ] active_start_time ] 
17. [ , [ @active_end_time = ] active_end_time ] 
18.  
19. 参数 
20. [ @jobid = ] job_id 
21.  
22. 将向其中添加调度的作业的作业标识号。job_id 的数据类型为 uniqueidentifier，默认设置为 NULL。 
23.  
24. [ @job_name = ] 'job_name' 
25.  
26. 作业的名称，调度即添加到该作业中。job_name 的数据类型为 sysname，默认设置为 NULL。 
27.  
28.  
29.  
30. 说明 必须指定 job_id 或 job_name，但不能两个都指定。 
31.  
32.  
33. [ @name = ] 'name' 
34.  
35. 调度的名称。name 的数据类型为 sysname，没有默认设置。 
36.  
37. [ @enabled = ] enabled 
38.  
39. 指明调度的当前状态。enabled 的数据类型为 tinyint，默认设置为 1（启用）。如果为 0，则不启用调度。禁用该调度时，不运行作业。 
40.  
41. [ @freq_type = ] freq_type 
42.  
43. 用于指明何时将执行作业的值。freq_type 的数据类型为 int，默认设置为 0，可以是下列值之一。 
44.  
45. 值 描述  
46. 1 一次  
47. 4 每天  
48. 8 每周  
49. 16 每月  
50. 32 每月，与 freq interval 相关  
51. 64 当 SQLServerAgent 服务启动时运行  
52. 128 计算机空闲时运行  
53.  
54.  
55. [ @freq_interval = ] freq_interval 
56.  
57. 作业执行的天数。freq_interval 的数据类型为 int，默认设置为 0，依赖于 freq_type 的值。 
58.  
59. freq_type 的值 对 freq_interval 的影响  
60. 1（一次） 未使用 freq_interval。  
61. 4（每天） 每个 freq_interval 日。  
62. 8（每周） freq_interval 为下面的一个或多个值（与 OR 逻辑运算符结合使用）：  
63. 1 = 星期日 
64. 2 = 星期一 
65. 4 = 星期二 
66. 8 = 星期三 
67. 16 = 星期四 
68. 32 = 星期五 
69. 64 = 星期六 
70.  
71. 16（每月） 每月的 freq_interval 日。  
72. 32（每月相对） freq_interval 为下列值之一：  
73. 1 = 星期日  
74. 2 = 星期一  
75. 3 = 星期二  
76. 4 = 星期三  
77. 5 = 星期四  
78. 6 = 星期五  
79. 7 = 星期六  
80. 8 = 日  
81. 9 = 工作日 
82. 10 = 周末 
83.  
84. 64（当 SQLServerAgent 服务启动时） 未使用 freq_interval。  
85. 128 未使用 freq_interval。  
86.  
87.  
88. [ @freq_subday_type = ] freq_subday_type 
89.  
90. 指定 freq_subday_interval 的单位。freq_subday_type 为 int 类型，其默认值为 0，且可以取下列值之一。 
91.  
92. 值 描述（单位）  
93. 0x1 在指定的时间  
94. 0x4 分钟  
95. 0x8 小时  
96.  
97.  
98. [ @freq_subday_interval = ] freq_subday_interval 
99.  
100. 作业每次执行之间要出现的 freq_subday_type 周期数。freq_subday_interval 的数据类型为 int，默认设置为 0。 
101.  
102. [ @freq_relative_interval = ] freq_relative_interval 
103.  
104. 如果 freq_interval 是 32（每月相对），则为每月中已调度作业的 freq_interval 的发生情况。freq_relative_interval 的数据类型为 int，默认设置为 0，可以是下列值之一。 
105.  
106. 值 描述（单位）  
107. 1 第一页  
108. 2 秒  
109. 4 第三个  
110. 8 第四个  
111. 16 最后一页  
112.  
113.  
114. [ @freq_recurrence_factor = ] freq_recurrence_factor 
115.  
116. 作业的已调度执行之间的周数或月数。只有当 freq_type 是 8、16 或 32 时，才使用 freq_recurrence_factor。freq_recurrence_factor 的数据类型为 int，默认设置为 0。 
117.  
118. [ @active_start_date = ] active_start_date 
119.  
120. 作业可开始执行的日期。active_start_date 的数据类型为 int，默认设置为 NULL，该值表示当天的日期。日期的格式为 YYYYMMDD。如果 active_start_date 不为 NULL，则日期必须大于或等于 19900101。 
121.  
122. [ @active_end_date = ] active_end_date 
123.  
124. 作业可停止执行的日期。active_end_date 的数据类型为 int，默认设置为 99991231，该值表示 9999 年 12 月 31 日。格式为 YYYYMMDD。 
125.  
126. [ @active_start_time = ] active_start_time 
127.  
128. 在 active_start_date 和 active_end_date 之间的任何一天开始执行作业的时间。active_start_time 的数据类型为 int，默认设置为 000000，该值表示 24 小时制的上午 12:00:00，并且必须使用格式 HHMMSS 进行输入。 
129.  
130. [ @active_end_time = ] active_end_time 
131.  
132. 在 active_start_date 和 active_end_date 之间的任何一天停止执行作业的时间。active_end_time 的数据类型为 int，默认设置为 235959，该值表示 24 小时制的下午 11:59:59，并且必须使用格式 HHMMSS 进行输入。 
133.  
134. 返回代码值 
135. 0（成功）或 1（失败） 
136.  
137. 结果集 
138. 无 
139.  
140. 注释 
141. SQL Server 企业管理器提供易于使用的图形方法来管理作业，建议使用该方法创建和管理作业基本结构。 
142.  
143. 权限 
144. 执行权限默认授予 public 角色。 
145.  
146. 示例 
147. 此示例假设已经创建用来备份数据库的 NightlyBackup 作业。它将作业添加到名为 ScheduledBackup 的调度中，并且在每天上午 1:00 执行。 
148.  
149. USE msdb 
150. EXEC sp_add_jobschedule @job_name = 'NightlyBackup',  
151. @name = 'ScheduledBackup', 
152. @freq_type = 4, -- daily 
153. @freq_interval = 1, 
154. @active_start_time = 10000 
155.  
156.  
157. 请参见 
158.  
159. 修改和查看作业 
160.  
161. sp_delete_jobschedule 
162.  
163. sp_help_jobschedule 
164.  
165. sp_update_jobschedule 
166.  
167. 系统存储过程 

本站文章旨在为该问题提供解决思路及关键性代码，并不能完成应该由网友自己完成的所有工作，请网友在仔细看文章并理解思路的基础上举一反三、灵活运用。