昨天发了篇 SQL Server 作业备份 http://www.cnblogs.com/Amaranthus/archive/2012/06/25/2561569.html
今天就加上powershell 把 作业同步 完善起来
核心导出作业的 代码 和 作业备份是相似的
alter
PROC
DumpJob (
@job
VARCHAR
(
100
))
AS
DECLARE
@retrun
NVARCHAR
(
max
)
DECLARE
@jobname
VARCHAR
(
30
),
@category_calss_i
INT
,
@category_calss
VARCHAR
(
50
),
@category_name
VARCHAR
(
50
)
,
@category_type
VARCHAR
(
30
),
@category_id
int
,
@category_type_i
int
SELECT
@jobname
=
'
powershell
'
,
@category_calss
=
''
,
@category_name
=
''
,
@category_type
=
''
SELECT
@jobname
=
@job
SELECT
@category_calss
=
CASE
WHEN
tshc.category_class
=
1
THEN
'
JOB
'
WHEN
tshc.category_class
=
2
THEN
'
ALERT
'
else
'
OPERATOR
'
END
,
@category_type
=
CASE
WHEN
tshc.category_type
=
1
THEN
'
LOCAL
'
WHEN
tshc.category_type
=
2
THEN
'
MULTI-SERVER
'
else
'
NONE
'
END
,
@category_name
=
tshc.name
,
@category_type_i
=
category_type
,
@category_calss_i
=
tshc.category_class
,
@category_id
=
tshc.category_id
FROM
msdb.dbo.sysjobs_view
AS
sv
INNER
JOIN
msdb.dbo.syscategories
AS
tshc
ON
sv.category_id
=
tshc.category_id
WHERE
(sv.name
=
@jobname
AND
tshc.category_class
=
1
)
SET
@retrun
=
'
BEGIN TRANSACTION
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
DECLARE @ReturnCode INT
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N
'''
+
@category_name
+
'''
AND category_class=
'
+
rtrim
(
@category_calss_i
)
+
'
)
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
BEGIN
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N
'''
+
@category_calss
+
'''
, @type=N
'''
+
@category_type
+
'''
, @name=N
'''
+
@category_name
+
''''
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
end
'
DECLARE
@EventLogLevel
INT
,
@EmailLevel
INT
,
@NetSendLevel
INT
,
@PageLevel
INT
DECLARE
@EmailLeveloprid
NVARCHAR
(
256
),
@NetSendLeveloprid
NVARCHAR
(
256
),
@PageLeveloprid
NVARCHAR
(
256
)
DECLARE
@isenable
INT
,
@description
NVARCHAR
(
1024
),
@owner_log_name
Nvarchar
(
512
),
@delete_level
INT
DECLARE
@jobId
UNIQUEIDENTIFIER
,
@start_step_id
INT
,
@server
NVARCHAR
(
512
)
SELECT
@EventLogLevel
=
sv.notify_level_eventlog
,
@EmailLevel
=
sv.notify_level_email
,
@NetSendLevel
=
sv.notify_level_netsend
,
@PageLevel
=
sv.notify_level_page
,
@EmailLeveloprid
=
ISNULL
((
SELECT
TOP
1
name
FROM
msdb..sysoperators
WHERE
id
=
sv.notify_email_operator_id),
''
)
,
@NetSendLeveloprid
=
ISNULL
((
SELECT
TOP
1
name
FROM
msdb..sysoperators
WHERE
id
=
sv.notify_netsend_operator_id),
''
)
,
@PageLeveloprid
=
ISNULL
((
SELECT
TOP
1
name
FROM
msdb..sysoperators
WHERE
id
=
sv.notify_page_operator_id),
''
)
,
@isenable
=
sv.enabled
,
@description
=
sv.description
,
@owner_log_name
=
ISNULL
(
suser_sname
(sv.owner_sid), N
''''
)
,
@delete_level
=
sv.delete_level
,
@jobId
=
sv.job_id
,
@start_step_id
=
start_step_id
,
@server
=
originating_server
FROM
msdb.dbo.sysjobs_view
AS
sv
WHERE
(sv.name
=
@jobname
and
sv.category_id
=
0
)
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
DECLARE @jobId BINARY(16)
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N
'''
+
@jobname
+
'''
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@enabled=
'
+
RTRIM
(
@isenable
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@notify_level_eventlog=
'
+
RTRIM
(
@EventLogLevel
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@notify_level_email=
'
+
RTRIM
(
@EmailLevel
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@notify_level_netsend=
'
+
RTRIM
(
@NetSendLevel
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@notify_level_page=
'
+
RTRIM
(
@PageLevel
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@notify_email_operator_name =
'''
+
RTRIM
(
@EmailLeveloprid
)
+
'''
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@notify_netsend_operator_name=
'''
+
RTRIM
(
@NetSendLeveloprid
)
+
'''
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@notify_page_operator_name=
'''
+
RTRIM
(
@PageLeveloprid
)
+
'''
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@delete_level=
'
+
RTRIM
(
@delete_level
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@description=N
'''
+
@description
+
'''
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@category_name=N
'''
+
@category_name
+
'''
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@owner_login_name=N
'''
+
@owner_log_name
+
'''
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@job_id = @jobId OUTPUT
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
'
--
SELECT * FROM msdb.dbo.syscategories
DECLARE
@step_id
INT
declare
@step_name
nvarchar
(
512
) ,
@cmdexec_success_code
INT
,
@on_success_action
INT
,
@on_success_step_id
INT
,
@on_fail_action
INT
,
@on_fail_step_id
INT
,
@retry_attempts
INT
,
@retry_interval
INT
,
@os_run_priority
INT
,
@subsystem
NVARCHAR
(
512
),
@database_name
NVARCHAR
(
512
),
@flags
INT
,
@command
NVARCHAR
(
max
)
DECLARE
jbcur
CURSOR
FOR
SELECT
step_id
FROM
msdb..sysjobsteps
WHERE
job_id
=
@jobid
ORDER
BY
step_id ;
OPEN
jbcur;
FETCH
NEXT
FROM
jbcur
INTO
@step_id
WHILE
@@FETCH_STATUS
=
0
BEGIN
SELECT
@step_name
=
step_name
,
@cmdexec_success_code
=
cmdexec_success_code
,
@on_success_action
=
on_success_action
,
@on_success_step_id
=
on_success_step_id
,
@on_fail_action
=
on_fail_action
,
@on_fail_step_id
=
on_fail_step_id
,
@retry_attempts
=
retry_attempts
,
@retry_interval
=
retry_interval
,
@os_run_priority
=
os_run_priority
,
@subsystem
=
subsystem
,
@database_name
=
database_name
,
@command
=
command
,
@flags
=
flags
FROM
msdb..sysjobsteps a
WHERE
job_id
=
@jobid
and
step_id
=
@step_id
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@step_name=N
'''
+
@step_name
+
'''
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@step_id=
'
+
RTRIM
(
@step_id
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@cmdexec_success_code=
'
+
RTRIM
(
@cmdexec_success_code
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@on_success_action=
'
+
RTRIM
(
@on_success_action
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@on_success_step_id=
'
+
RTRIM
(
@on_success_step_id
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@on_fail_action=
'
+
RTRIM
(
@on_fail_action
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@on_fail_step_id=
'
+
RTRIM
(
@on_fail_step_id
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@retry_attempts=
'
+
RTRIM
(
@retry_attempts
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@retry_interval=
'
+
RTRIM
(
@retry_interval
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@os_run_priority=
'
+
RTRIM
(
@os_run_priority
)
+
'
, @subsystem=N
'''
+
@subsystem
+
'''
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@database_name=N
'''
+
@database_name
+
'''
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@flags=
'
+
RTRIM
(
@flags
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@command=N
'''
+
REPLACE
(
@command
,
''''
,
''''''
)
+
''''
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
'
FETCH
NEXT
FROM
jbcur
INTO
@step_id
END
CLOSE
jbcur
DEALLOCATE
jbcur
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id =
'
+
rtrim
(
@start_step_id
)
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
'
DECLARE
@enabled
INT
,
@freq_type
INT
,
@freq_interval
INT
,
@freq_subday_type
INT
,
@freq_subday_interval
INT
,
@freq_relative_interval
INT
,
@freq_recurrence_factor
INT
,
@active_start_date
INT
,
@active_end_date
INT
,
@active_start_time
INT
,
@active_end_time
INT
,
@name
VARCHAR
(
512
)
SELECT
@name
=
a.name
,
@enabled
=
enabled
,
@freq_interval
=
freq_interval
,
@freq_type
=
freq_type
,
@freq_subday_type
=
freq_subday_type
,
@freq_subday_interval
=
freq_subday_interval
,
@freq_relative_interval
=
freq_relative_interval
,
@freq_recurrence_factor
=
freq_recurrence_factor
,
@active_start_date
=
active_start_date
,
@active_end_date
=
active_end_date
,
@active_start_time
=
active_start_time
,
@active_end_time
=
active_end_time
FROM
msdb..sysschedules a
INNER
JOIN
msdb.dbo.sysjobschedules b
ON
a.schedule_id
=
b.schedule_id
WHERE
job_id
=
@jobId
IF
(
@name
IS
not
null
)
begin
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N
'''
+
@name
+
'''
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@enabled=
'
+
RTRIM
(
@enabled
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@freq_type=
'
+
RTRIM
(
@freq_type
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@freq_interval=
'
+
RTRIM
(
@freq_interval
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@freq_subday_type=
'
+
RTRIM
(
@freq_subday_type
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@freq_subday_interval=
'
+
RTRIM
(
@freq_subday_interval
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@freq_relative_interval=
'
+
RTRIM
(
@freq_relative_interval
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@freq_recurrence_factor=
'
+
RTRIM
(
@freq_recurrence_factor
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@active_start_date=
'
+
RTRIM
(
@active_start_date
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@active_end_date=
'
+
RTRIM
(
@active_end_date
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@active_start_time=
'
+
RTRIM
(
@active_start_time
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@active_end_time=
'
+
RTRIM
(
@active_end_time
)
+
'
,
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
@schedule_uid=N
'''
+
RTRIM
(
NEWID
())
+
''''
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
'
END
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N
''
(local)
'''
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
COMMIT TRANSACTION
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
GOTO EndSave
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
QuitWithRollback:
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
IF(@@TRANCOUNT>0)ROLLBACK TRANSACTION
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
EndSave:
'
SET
@retrun
=
@retrun
+
CHAR
(
13
)
+
CHAR
(
10
)
+
'
'
select
@retrun
我创建了一个存储过程，用来导出 作业，只有用powershell 脚本来实现同步，你可以powershell 脚本放入 sqlagent 中 定时运行起到同步的效果
一下是powershell 代码：
$server
=
"
(local)
"
$uid
=
"
sa
"
$db
=
"
master
"
$pwd
=
"
fanzhouqi
"
$mailprfname
=
"
sina
"
$recipients
=
"
32116057@qq.com
"
$subject
=
'System Log'
function
execproc(
$message
)
{
$SqlConnection
= New-
Object System.Data.SqlClient.SqlConnection
$CnnString
=
"
Server = $server; Database = $db;User Id = $uid; Password = $pwd
"
$SqlConnection
.ConnectionString =
$CnnString
$CC
=
$SqlConnection
.CreateCommand();
$CC
.CommandText=
$message
$adapter
= New-Object System.Data.SqlClient.SqlDataAdapter
$CC
$dataset
= New-
Object System.Data.DataSet
#
$SqlConnection.SelectCommand = $CC
if
(
-not
(
$SqlConnection
.State
-like
"
Open
"
)) {
$SqlConnection
.Open() }
$adapter
.Fill(
$dataset
) |out-
null
$dataset
.Tables[0].Rows[0][0
]
$SqlConnection
.Close();
}
function
execsql(
$message
)
{
$SqlConnection
= New-
Object System.Data.SqlClient.SqlConnection
$CnnString
=
"
Server = fanr-pc\sql2012; Database = $db;User Id = $uid; Password = $pwd
"
$SqlConnection
.ConnectionString =
$CnnString
$CC
=
$SqlConnection
.CreateCommand();
if
(
-not
(
$SqlConnection
.State
-like
"
Open
"
)) {
$SqlConnection
.Open() }
$cc
.CommandText=
$message
$cc
.ExecuteNonQuery()|out-
null
$SqlConnection
.Close();
}
$jobscript
= execproc
"
EXEC master..DumpJob @job = 'backup'
"
#
$jobscript
execsql
$jobscript
有什么问题可以联系我：如果blog 的代码没办法使用也可以 加我qq 联系我，问我要。qq：32116057 fanr
