tambc

?1 -- ?2005-9-6/11:49?上生成的腳本?
?2 -- ?由:?IBM\Administrator?
?3 -- ?服務(wù)器:?(LOCAL)?
?4
?5 BEGIN ? TRANSACTION ?
?6 DECLARE ? @JobID ? BINARY ( 16 )?
?7 DECLARE ? @ReturnCode ? INT ?
?8 SELECT ? @ReturnCode ? = ? 0 ?
?9 IF ?( SELECT ? COUNT ( * )? FROM ?msdb.dbo.syscategories? WHERE ?name? = ?N’ [ Uncategorized?(Local) ] ’)? < ? 1 ?
10 EXECUTE ?msdb.dbo.sp_add_category? @name ? = ?N’ [ Uncategorized?(Local) ] ’?
11
12 -- ?刪除同名的警報（如果有的話）。?
13 SELECT ? @JobID ? = ?job_id?
14 FROM ?msdb.dbo.sysjobs?
15 WHERE ?(name? = ?N’databaseName?備份’)?
16 IF ?( @JobID ? IS ? NOT ? NULL )?
17 BEGIN ?
18 -- ?檢查此作業(yè)是否為多重服務(wù)器作業(yè)?
19 IF ?( EXISTS ?( SELECT ? * ?
20 FROM ?msdb.dbo.sysjobservers?
21 WHERE ?(job_id? = ? @JobID )? AND ?(server_id? <> ? 0 )))?
22 BEGIN ?
23 -- ?已經(jīng)存在，因而終止腳本?
24 RAISERROR ?(N’無法導(dǎo)入作業(yè)“databaseName?備份”，因為已經(jīng)有相同名稱的多重服務(wù)器作業(yè)。’,? 16 ,? 1 )?
25 GOTO ?QuitWithRollback?
26 END ?
27 ELSE ?
28 -- ?刪除［本地］作業(yè)?
29 EXECUTE ?msdb.dbo.sp_delete_job? @job_name ? = ?N’databaseName?備份’?
30 SELECT ? @JobID ? = ? NULL ?
31 END ?
32
33 BEGIN ?
34
35 -- ?添加作業(yè)?
36 EXECUTE ? @ReturnCode ? = ?msdb.dbo.sp_add_job? @job_id ? = ? @JobID ?OUTPUT?,? @job_name ? = ?N’databaseName?備份’,? @owner_login_name ? = ?N’IBM\Administrator’,? @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 ?
37 IF ?( @@ERROR ? <> ? 0 ? OR ? @ReturnCode ? <> ? 0 )? GOTO ?QuitWithRollback?
38
39 -- ?添加作業(yè)步驟?
40 EXECUTE ? @ReturnCode ? = ?msdb.dbo.sp_add_jobstep? @job_id ? = ? @JobID ,? @step_id ? = ? 1 ,? @step_name ? = ?N’備份databaseName’,? @command ? = ?N’ declare ? @filename ? nvarchar ( 100 )?
41 set ? @filename = ’’E:\ temp \WCMBAK\databaseName’’? + ? LTRIM ( STR ( YEAR ( GETDATE ())))? + ?’’ - ’’? + ? LTRIM ( STR ( MONTH ( GETDATE ())))? + ?’’ - ’’? + ? LTRIM ( STR ( DAY ( GETDATE ())))? + ’’.bak’’?
42 BACKUP ? DATABASE ? [ databaseName ] ? TO ? DISK ? = ? @filename ? WITH ?INIT?,?NOUNLOAD?,?NAME? = ?N’’databaseName?備份’’,?NOSKIP?,?STATS? = ? 10 ,?NOFORMAT’,? @database_name ? = ?N’master’,? @server ? = ?N’’,? @database_user_name ? = ?N’’,? @subsystem ? = ?N’TSQL’,? @cmdexec_success_code ? = ? 0 ,? @flags ? = ? 0 ,? @retry_attempts ? = ? 0 ,? @retry_interval ? = ? 0 ,? @output_file_name ? = ?N’’,? @on_success_step_id ? = ? 2 ,? @on_success_action ? = ? 4 ,? @on_fail_step_id ? = ? 2 ,? @on_fail_action ? = ? 4 ?
43 IF ?( @@ERROR ? <> ? 0 ? OR ? @ReturnCode ? <> ? 0 )? GOTO ?QuitWithRollback?
44 EXECUTE ? @ReturnCode ? = ?msdb.dbo.sp_add_jobstep? @job_id ? = ? @JobID ,? @step_id ? = ? 2 ,? @step_name ? = ?N’備份XJOB’,? @command ? = ?N’ declare ? @filename ? nvarchar ( 100 )?
45 set ? @filename = ’’E:\ temp \DBBAK\DB’’? + ? LTRIM ( STR ( YEAR ( GETDATE ())))? + ?’’ - ’’? + ? LTRIM ( STR ( MONTH ( GETDATE ())))? + ?’’ - ’’? + ? LTRIM ( STR ( DAY ( GETDATE ())))? + ’’.bak’’?
46 BACKUP ? DATABASE ? [ xjob ] ? TO ? DISK ? = ? @filename ? WITH ?INIT?,?NOUNLOAD?,?NAME? = ?N’’xjob?備份’’,?NOSKIP?,?STATS? = ? 10 ,?NOFORMAT’,? @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 ?
47 IF ?( @@ERROR ? <> ? 0 ? OR ? @ReturnCode ? <> ? 0 )? GOTO ?QuitWithRollback?
48 EXECUTE ? @ReturnCode ? = ?msdb.dbo.sp_update_job? @job_id ? = ? @JobID ,? @start_step_id ? = ? 1 ?
49
50 IF ?( @@ERROR ? <> ? 0 ? OR ? @ReturnCode ? <> ? 0 )? GOTO ?QuitWithRollback?
51
52 -- ?添加作業(yè)調(diào)度?
53 EXECUTE ? @ReturnCode ? = ?msdb.dbo.sp_add_jobschedule? @job_id ? = ? @JobID ,? @name ? = ?N’第? 1 ?調(diào)度’,? @enabled ? = ? 1 ,? @freq_type ? = ? 8 ,? @active_start_date ? = ? 20050906 ,? @active_start_time ? = ? 0 ,? @freq_interval ? = ? 42 ,? @freq_subday_type ? = ? 1 ,? @freq_subday_interval ? = ? 0 ,? @freq_relative_interval ? = ? 0 ,? @freq_recurrence_factor ? = ? 1 ,? @active_end_date ? = ? 99991231 ,? @active_end_time ? = ? 235959 ?
54 IF ?( @@ERROR ? <> ? 0 ? OR ? @ReturnCode ? <> ? 0 )? GOTO ?QuitWithRollback?
55
56 -- ?添加目標(biāo)服務(wù)器?
57 EXECUTE ? @ReturnCode ? = ?msdb.dbo.sp_add_jobserver? @job_id ? = ? @JobID ,? @server_name ? = ?N’(local)’?
58 IF ?( @@ERROR ? <> ? 0 ? OR ? @ReturnCode ? <> ? 0 )? GOTO ?QuitWithRollback?
59
60 END ?
61 COMMIT ? TRANSACTION ?
62 GOTO ?EndSave?
63 QuitWithRollback:?
64 IF ?( @@TRANCOUNT ? > ? 0 )? ROLLBACK ? TRANSACTION ?
65 EndSave: