要数据库系统定时执行某操作需要三步:
sp_add_job、sp_add_jobstep、sp_add_jobschedule
具体自己看帮助,一个个解释要累死人的:)
下面是创建一个备份数据库计划的例子,结合帮助自己去体会吧!
declare @id BINARY(16)
exec msdb..sp_add_job @job_name = N'testbak1 备份 2', @enabled = 1, @start_step_id = 1, @notify_level_eventlog = 2, @delete_level = 0, @job_id = @id OUTPUT
select @id
exec msdb..sp_add_jobstep @job_id = @id , @step_id = 1, @cmdexec_success_code = 0, @on_success_action = 1, @on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0, @os_run_priority = 0, @flags = 0, @step_name = N'第 1 步', @subsystem = N'TSQL', @command = N'BACKUP DATABASE [testbak1] TO DISK = N''G:/testbak12.bak'' WITH NOINIT , NOUNLOAD , NAME = N''testbak1 备份2'', NOSKIP , STATS = 10, NOFORMAT '
exec msdb..sp_add_jobschedule @job_id = @id, @name = N'第 1 调度', @enabled = 1, @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_start_date = 20020912, @active_end_date = 99991231, @active_start_time = 0, @active_end_time = 235959
exec msdb..sp_add_jobserver @job_id = @id