blog.Toby

  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  130 随笔 :: 2 文章 :: 150 评论 :: 0 Trackbacks

 

USE [msdb]

GO

/****** 对象:  Job [pdw远程备份到192.168.36.38(每周7覆盖更新)]    脚本日期: 12/21/2007 09:37:05 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** 对象:  JobCategory [Database Maintenance]    脚本日期: 12/21/2007 09:37:05 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

END

 

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'pdw远程备份到192.168.36.38(每周7覆盖更新)',

                   @enabled=1,

                   @notify_level_eventlog=2,

                   @notify_level_email=0,

                   @notify_level_netsend=0,

                   @notify_level_page=0,

                   @delete_level=0,

                   @description=N'pdw远程备份到192.168.36.38(每周7覆盖更新)',

                   @category_name=N'Database Maintenance',

                   @owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** 对象:  Step [a net use  \\192.168.36.38]    脚本日期: 12/21/2007 09:37:06 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'a net use  \\192.168.36.38',

                   @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=10,

                   @retry_interval=1,

                   @os_run_priority=0, @subsystem=N'TSQL',

                   @command=N'--pdw远程备份设备安装到192.168.36.38

master..xp_cmdshell ''net use  \\192.168.36.38 ****** /user:editor''',

                   @database_name=N'master',

                   @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** 对象:  Step [BACKUP DATABASE [CSRC_RS_NEW]]]    脚本日期: 12/21/2007 09:37:06 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BACKUP DATABASE [CSRC_RS_NEW]',

                   @step_id=2,

                   @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=1,

                   @os_run_priority=0, @subsystem=N'TSQL',

                   @command=N'BACKUP DATABASE [CSRC_RS_NEW] TO  DISK = N''\\192.168.36.38\d$\data_bak\CSRC_RS_NEW.bak'' WITH  INIT ,  NOUNLOAD ,  NAME = N''CSRC_RS_NEW'',  NOSKIP ,  STATS = 10,  NOFORMAT',

                   @database_name=N'CSRC_RS_NEW',

                   @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

         IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

 
 

posted on 2007-12-21 09:54 渠上月 阅读(195) 评论(0)  编辑  收藏 所属分类: sql (sqlServer)

只有注册用户登录后才能发表评论。


网站导航: