创建数据库作业

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

USE [msdb]
GO

/****** Object:  Job [DatabaWorkFlowSql]    Script Date: 2014/11/17 19:48:02 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 2014/11/17 19:48:02 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB',@type=N'LOCAL',@name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DatabaWorkFlowSql',@enabled=1,@notify_level_eventlog=1,@notify_level_email=1,@notify_level_netsend=1,@notify_level_page=1,@delete_level=0,@description=N'日志传送的说明内容。
1.在TableInfo里面创建一个表
2.插入数据
3.备份数据库',@category_name=N'[Uncategorized (Local)]',@owner_login_name=N'ROBORTLY勇',@notify_email_operator_name=N'luoyong',@notify_netsend_operator_name=N'luoyong',@notify_page_operator_name=N'luoyong',@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [createTable]    Script Date: 2014/11/17 19:48:02 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email?protected],@step_name=N'createTable',@step_id=1,@cmdexec_success_code=0,@on_success_action=3,@on_success_step_id=0,@on_fail_action=2,@on_fail_step_id=0,@retry_attempts=0,@retry_interval=0,@os_run_priority=0,@subsystem=N'TSQL',@command=N'create table WorkFlowTable
(
	wfid  int,datas  nvarchar(10)
)',@database_name=N'TableInfo',@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [insertValues]    Script Date: 2014/11/17 19:48:02 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email?protected],@step_name=N'insertValues',@step_id=2,@command=N'insert into workflowTable values(1,''luoyong'')',@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [备份]    Script Date: 2014/11/17 19:48:02 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email?protected],@step_name=N'备份',@step_id=3,@on_success_action=1,@command=N'BACKUP DATABASE [TableInfo] TO  [luoyongImportant] WITH NOFORMAT,NOINIT,NAME = N''TableInfo-完整 数据库 备份'',SKIP,NOREWIND,NOUNLOAD,STATS = 10
GO',@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId,@start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @[email?protected],@name=N'Plan',@freq_type=1,@freq_interval=0,@freq_subday_type=0,@freq_subday_interval=0,@freq_relative_interval=0,@freq_recurrence_factor=0,@active_start_date=20141117,@active_end_date=99991231,@active_start_time=194600,@active_end_time=235959,@schedule_uid=N'10456071-9a44-4c6a-82b7-7243899c4db6'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId,@server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!