針對logins和agent jobs在不同SQL Server Instance同步，
我以前都是透過SSIS來處理，但我真心覺得，MS預設應該在SSMS中新增同步物件功能。
由於AGs是DB Level，所以Server Level相關objects(Linked server,logins,agent jobs,permission,alert…等)都不會自動同步到其他secondary，
透過人工手動維護有時可能發生遺漏，而且透過SSIS傳送logins，預設會更改原來密碼並disabled(有點美中不足)，
至於傳送agent jobs則相當完美。
由於同事不太熟悉SSIS，所以我打算統一使用TSQL來處理logins和agent jobs的同步作業，
我透過GDD並修改相關scripts如下，另外，請勿直接複製並執行在正式環境，一切後果請自行負責。
--Sync logins
USE master;
GO
IF (OBJECT_ID('dbo.dba_SyncLogins') IS NULL) EXEC('CREATE PROCEDURE dbo.dba_SyncLogins AS 0;');
GO
ALTER PROCEDURE dbo.dba_SyncLogins
@LinkedServerPrimary sysname=NULL,
@AllowDropLogins bit=0,
@PrintOnly bit=0
AS
SET NOCOUNT ON;
DECLARE @sql nvarchar(max), @msg nvarchar(max);
IF (@LinkedServerPrimary NOT IN (SELECT [name]
FROM sys.servers)) BEGIN;
THROW 50000, N'Primary replica is not a linked server.', 16;
RETURN;
END;
IF (@LinkedServerPrimary IS NULL) BEGIN;
SELECT @LinkedServerPrimary=primary_replica
FROM sys.dm_hadr_availability_group_states;
IF (@@ROWCOUNT>1) BEGIN;
THROW 50000, N'More than one availability group exists on server, please specify @LinkedServerPrimary.', 16;
RETURN;
END;
IF (@LinkedServerPrimary IS NULL) BEGIN;
THROW 50000, N'No availability group found, please specify @LinkedServerPrimary.', 16;
RETURN;
END;
END;
IF (@LinkedServerPrimary=@@SERVERNAME) BEGIN;
PRINT N'This server is the primary replica. No changes will be made.';
RETURN;
END;
--- These are the logins (Windows user and groups, SQL logins) from
--- the primary replica.
DECLARE @primaryLogins TABLE (
[name] sysname NOT NULL,
[sid] varbinary(85) NOT NULL,
[type] char(1) NOT NULL,
is_disabled bit NULL,
default_database_name sysname NULL,
default_language_name sysname NULL,
is_policy_checked bit NULL,
is_expiration_checked bit NULL,
password_hash varbinary(256) NULL,
PRIMARY KEY CLUSTERED ([sid])
);
SET @sql=N'
SELECT sp.[name], sp.[sid], sp.[type], sp.is_disabled, sp.default_database_name,
sp.default_language_name, l.is_policy_checked, l.is_expiration_checked, l.password_hash
FROM ['+@LinkedServerPrimary+'].master.sys.server_principals AS sp
LEFT JOIN ['+@LinkedServerPrimary+'].master.sys.sql_logins AS l ON sp.[sid]=l.[sid]
WHERE sp.[type] IN (''U'', ''G'', ''S'') AND
UPPER(sp.[name]) NOT LIKE ''NT SERVICE\%'' AND
sp.[name] NOT IN (''NT AUTHORITY\SYSTEM'')';
INSERT INTO @primaryLogins
EXECUTE master.sys.sp_executesql @sql;
--- These are the server roles on the primary replica.
DECLARE @primaryRoles TABLE (
[sid] varbinary(85) NOT NULL,
[name] sysname NOT NULL,
PRIMARY KEY CLUSTERED ([sid])
);
SET @sql=N'
SELECT sr.[sid], sr.[name]
FROM ['+@LinkedServerPrimary+'].master.sys.server_principals AS sr
WHERE sr.is_fixed_role=0 AND
sr.[type]=''R''';
INSERT INTO @primaryRoles
EXECUTE master.sys.sp_executesql @sql;
--- These are the role members of the server roles on
--- the primary replica.
DECLARE @primaryMembers TABLE (
role_sid varbinary(85) NOT NULL,
member_sid varbinary(85) NOT NULL,
PRIMARY KEY CLUSTERED (role_sid, member_sid)
);
SET @sql=N'
SELECT r.[sid], m.[sid]
FROM ['+@LinkedServerPrimary+N'].master.sys.server_principals AS r
INNER JOIN ['+@LinkedServerPrimary+N'].master.sys.server_role_members AS rm ON r.principal_id=rm.role_principal_id
INNER JOIN ['+@LinkedServerPrimary+N'].master.sys.server_principals AS m ON rm.member_principal_id=m.principal_id';
INSERT INTO @primaryMembers
EXECUTE master.sys.sp_executesql @sql;
--- These are the server-level permissions on the
--- primary replica.
DECLARE @primaryPermissions TABLE (
state_desc nvarchar(120) NOT NULL,
[permission_name] nvarchar(256) NOT NULL,
principal_name sysname NOT NULL,
PRIMARY KEY CLUSTERED ([permission_name], principal_name)
);
SET @sql=N'
SELECT p.state_desc, p.[permission_name], sp.[name]
FROM ['+@LinkedServerPrimary+'].master.sys.server_permissions AS p
INNER JOIN ['+@LinkedServerPrimary+'].master.sys.server_principals AS sp ON p.grantee_principal_id=sp.principal_id
WHERE p.class=100';
INSERT INTO @primaryPermissions
EXECUTE master.sys.sp_executesql @sql;
--- This table variable contains the "run queue" of all commands
--- we want to execute on the local (secondary) replica, ordered
--- by "seq".
DECLARE @queue TABLE (
seq int IDENTITY(1, 1) NOT NULL,
[sql] nvarchar(max) NOT NULL,
PRIMARY KEY CLUSTERED (seq)
);
-------------------------------------------------------------------------------
--- Login doesn't exist on the primary - DROP.
INSERT INTO @queue
([sql])
SELECT N'
DROP LOGIN ['+sp.[name]+N'];'
FROM master.sys.server_principals AS sp
WHERE sp.[type] IN ('U', 'G', 'S') AND
UPPER(sp.[name]) NOT LIKE 'NT SERVICE\%' AND
sp.[name] NOT IN ('NT AUTHORITY\SYSTEM') AND
sp.[sid] NOT IN (SELECT [sid]
FROM @primaryLogins) AND
@AllowDropLogins=1;
--- Login doesn't exist on the secondary - CREATE.
INSERT INTO @queue
([sql])
SELECT N'
CREATE LOGIN ['+p.[name]+'] '+(CASE
WHEN p.[type]='S'
THEN N'WITH PASSWORD=0x'+CONVERT(nvarchar(max), p.password_hash, 2)+N' HASHED, CHECK_POLICY=OFF, SID=0x'+CONVERT(nvarchar(max), p.[sid], 2)+N', '
WHEN p.[type] IN ('U', 'G')
THEN N'FROM WINDOWS WITH ' END)+
N'DEFAULT_DATABASE=['+p.default_database_name+N']'+
ISNULL(N', DEFAULT_LANGUAGE='+p.default_language_name, N'')+N';'
FROM @primaryLogins AS p
WHERE p.[sid] NOT IN (SELECT [sid]
FROM master.sys.server_principals) AND
p.[type] IN ('U', 'G', 'S');
--- Login exists but has been enabled/disabled - ALTER.
INSERT INTO @queue
([sql])
SELECT N'
ALTER LOGIN ['+ISNULL(sp.[name], x.[name])+']'+
(CASE WHEN x.is_disabled=0 AND sp.is_disabled=1 THEN N' ENABLE'
WHEN x.is_disabled=1 AND (sp.is_disabled=0 OR sp.[sid] IS NULL) THEN N' DISABLE' END)+N';'
FROM @primaryLogins AS x
LEFT JOIN master.sys.server_principals AS sp ON x.[sid]=sp.[sid]
WHERE x.is_disabled!=sp.is_disabled OR
x.is_disabled=1 AND sp.[sid] IS NULL;
--- Login exists but has changed in some respect - ALTER.
INSERT INTO @queue
([sql])
SELECT N'
ALTER LOGIN ['+sp.[name]+'] WITH '+
SUBSTRING(
(CASE WHEN x.password_hash!=l.password_hash
THEN N', PASSWORD=0x'+CONVERT(nvarchar(max), x.password_hash, 2)+N' HASHED, CHECK_POLICY=OFF' --added CHECK_POLICY=OFF by Rico
ELSE N'' END)+
(CASE WHEN ISNULL(x.default_database_name, N'master')!=ISNULL(sp.default_database_name, N'master')
THEN ', DEFAULT_DATABASE=['+x.default_database_name+N']'
ELSE N'' END)+
(CASE WHEN x.default_language_name!=sp.default_language_name
THEN ', DEFAULT_LANGUAGE='+x.default_language_name
ELSE N'' END)+
(CASE WHEN x.[name]!=sp.[name]
THEN ', NAME=['+x.[name]+N']'
ELSE N'' END)+
(CASE WHEN x.is_policy_checked!=l.is_policy_checked
THEN ', CHECK_POLICY='+(CASE x.is_policy_checked WHEN 1 THEN N'ON' ELSE N'OFF' END)
ELSE N'' END)+
(CASE WHEN x.is_expiration_checked!=l.is_expiration_checked
THEN ', CHECK_EXPIRATION='+(CASE x.is_expiration_checked WHEN 1 THEN N'ON' ELSE N'OFF' END)
ELSE N'' END), 3, 10000)+N';'
FROM @primaryLogins AS x
INNER JOIN master.sys.server_principals AS sp ON x.[sid]=sp.[sid]
LEFT JOIN master.sys.sql_logins AS l ON sp.[sid]=l.[sid]
WHERE x.password_hash!=l.password_hash OR
ISNULL(x.default_database_name, N'master')!=ISNULL(sp.default_database_name, N'master') OR
ISNULL(x.default_language_name, N'us_english')!=ISNULL(sp.default_language_name, N'us_english') OR
x.[name]!=sp.[name] OR
ISNULL(x.is_policy_checked, 0)!=ISNULL(l.is_policy_checked, 0) OR
ISNULL(x.is_expiration_checked, 0)!=ISNULL(l.is_expiration_checked, 0);
-------------------------------------------------------------------------------
--- Roles that don't exist on the primary - DROP.
INSERT INTO @queue
([sql])
SELECT N'
DROP ROLE ['+sp.[name]+N'];'
FROM master.sys.server_principals AS sp
WHERE is_fixed_role=0 AND
sp.[type]='R' AND
sp.[sid] NOT IN (SELECT [sid]
FROM @primaryRoles);
--- Roles that don't exist on the secondary - CREATE.
INSERT INTO @queue
([sql])
SELECT N'
CREATE SERVER ROLE ['+r.[name]+N'];'
FROM @primaryRoles AS r
WHERE [sid] NOT IN (
SELECT [sid]
FROM sys.server_principals
WHERE is_fixed_role=0 AND
[type]='R');
-------------------------------------------------------------------------------
--- Revoke role memberships:
INSERT INTO @queue
([sql])
SELECT N'
ALTER SERVER ROLE ['+r.[name]+N'] DROP MEMBER ['+m.[name]+N'];'
FROM sys.server_role_members AS rm
INNER JOIN sys.server_principals AS r ON r.principal_id=rm.role_principal_id
INNER JOIN sys.server_principals AS m ON m.principal_id=rm.member_principal_id
LEFT JOIN @primaryMembers AS pm ON pm.member_sid=m.[sid] AND pm.role_sid=r.[sid]
WHERE pm.role_sid IS NULL;
--- Add server role memberships:
INSERT INTO @queue
([sql])
SELECT N'
ALTER SERVER ROLE ['+pr.[name]+N'] ADD MEMBER ['+pl.[name]+N'];'
FROM @primaryMembers AS pm
INNER JOIN @primaryLogins AS pl ON pm.member_sid=pl.[sid]
INNER JOIN @primaryRoles AS pr ON pm.role_sid=pr.[sid]
LEFT JOIN sys.server_principals AS r ON pm.role_sid=r.[sid] AND r.[type]='R'
LEFT JOIN sys.server_principals AS m ON pm.member_sid=m.[sid]
LEFT JOIN sys.server_role_members AS rm ON r.principal_id=rm.role_principal_id AND m.principal_id=rm.member_principal_id
WHERE rm.role_principal_id IS NULL;
-------------------------------------------------------------------------------
--- GRANT/DENY server-level permissions:
INSERT INTO @queue
([sql])
SELECT N'
'+pp.state_desc+N' '+pp.[permission_name]+N' TO ['+pp.principal_name+'];'
FROM @primaryPermissions AS pp
INNER JOIN sys.server_principals AS sp ON pp.principal_name=sp.[name]
LEFT JOIN sys.server_permissions AS p ON
p.grantee_principal_id=sp.principal_id AND
p.[permission_name] COLLATE database_default=pp.[permission_name] AND
p.class=100
WHERE pp.state_desc!=p.state_desc COLLATE database_default;
-------------------------------------------------------------------------------
--- Ready to roll:
SET @sql=N'';
SELECT @sql=@sql+[sql]
FROM @queue
ORDER BY seq;
--- @PrintOnly=1: PRINT the queue.
WHILE (@PrintOnly=1 AND @sql!=N'') BEGIN;
PRINT LEFT(@sql, CHARINDEX(CHAR(13), @sql+CHAR(13))-1);
SET @sql=SUBSTRING(@sql, CHARINDEX(CHAR(13), @sql+CHAR(13))+2, LEN(@sql));
END;
--- @PrintOnly=0: Execute the queue.
IF (@PrintOnly=0)
EXECUTE master.sys.sp_executesql @sql;
GO
--Sync agent jobs ,just only print
USE [msdb]
GO
PRINT N'Use [msdb]'
/*******************************************
Create Job Categories
********************************************/
PRINT N'--Create Job Categories'
PRINT char(13) + char(10)
PRINT N'DECLARE @ReturnCode INT';
PRINT N'SELECT @ReturnCode = 0';
DECLARE @owner_login_name sysname='sa'
DECLARE @notify_email_operator_name sysname='dbasupport'
--set opersator name here. Operator needs to already exist on target instance
DECLARE @categoryname sysname
DECLARE @mysqlstatement nvarchar(max)='';
Declare categorycursor CURSOR FAST_FORWARD FOR
SELECT name
FROM msdb.dbo.syscategories
WHERE category_class = 1
OPEN categorycursor
PRINT N'BEGIN TRANSACTION';
PRINT char(13) + char(10);
FETCH NEXT FROM categorycursor
INTO @categoryname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT N'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name= ' + char(39) + @categoryname + char(39) + N' AND category_class=1)';
PRINT N'BEGIN'
PRINT N'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name=N''' + @categoryname + char(39);
PRINT N'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback';
PRINT N'END'
PRINT char(13) + char(10);
FETCH NEXT FROM categorycursor
INTO @categoryname
END
CLOSE categorycursor;
DEALLOCATE categorycursor;
PRINT char(13) + char(10);
PRINT N'COMMIT TRANSACTION'
PRINT N'GOTO EndSave'
PRINT N'QuitWithRollback:'
PRINT N' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'
PRINT N'EndSave:'
PRINT N'GO'
GO
/*******************************************
Create Schedules
********************************************/
PRINT N'--Create Schedules'
PRINT char(13) + char(10)
PRINT N'DECLARE @ReturnCode INT';
PRINT N'SELECT @ReturnCode = 0';
DECLARE @schedule_name sysname
DECLARE @enabled int
DECLARE @freq_type int
DECLARE @freq_interval int
DECLARE @freq_subday_type int
DECLARE @freq_subday_interval int
DECLARE @freq_recurrence_factor int
DECLARE @active_start_date int
DECLARE @active_end_date int
DECLARE @active_start_time int
DECLARE @active_end_time int
Declare schedulecursor CURSOR FAST_FORWARD FOR
select [name] as schedule_name
, [enabled]
, freq_type
, freq_interval
, freq_subday_type
, freq_subday_interval
, freq_recurrence_factor
, active_start_date
, active_end_date
, active_start_time
, active_end_time
from msdb.dbo.sysschedules
OPEN schedulecursor
PRINT N'BEGIN TRANSACTION';
PRINT char(13) + char(10);
FETCH NEXT FROM schedulecursor
INTO @schedule_name
,@enabled
,@freq_type
,@freq_interval
,@freq_subday_type
,@freq_subday_interval
,@freq_recurrence_factor
,@active_start_date
,@active_end_date
,@active_start_time
,@active_end_time
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT N'IF NOT EXISTS (SELECT name FROM msdb.dbo.sysschedules WHERE name= ' + char(39) + @schedule_name + char(39) + ')';
PRINT N'BEGIN'
PRINT N'EXEC @ReturnCode = sp_add_schedule';
PRINT N'@schedule_name = ' + '''' + cast(@schedule_name as nvarchar(max)) + '''' + ',';
PRINT N'@enabled = ' + cast(@enabled as nvarchar(max)) + ',';
PRINT N'@freq_type= ' + cast(@freq_type as nvarchar(max)) + ',';
PRINT N'@freq_interval= ' + cast(@freq_interval as nvarchar(max)) + ',';
PRINT N'@freq_subday_type= ' + cast(@freq_subday_type as nvarchar(max)) + ',';
PRINT N'@freq_subday_interval= ' + cast(@freq_subday_interval as nvarchar(max)) + ',';
PRINT N'@freq_recurrence_factor= ' + cast(@freq_recurrence_factor as nvarchar(max)) + ',';
PRINT N'@active_start_date= ' + cast(@active_start_date as nvarchar(max)) + ',';
PRINT N'@active_end_date= ' + cast(@active_end_date as nvarchar(max)) + ',';
PRINT N'@active_start_time= ' + cast(@active_start_time as nvarchar(max)) + ',';
PRINT N'@active_end_time= ' + cast(@active_end_time as nvarchar(max)) + ',';
PRINT N'@owner_login_name= ' + char(39) + cast(@owner_login_name as nvarchar(max)) + char(39);
PRINT char(13) + char(10);
PRINT N'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback';
PRINT N'END'
PRINT char(13) + char(10);
FETCH NEXT FROM schedulecursor
INTO @schedule_name
,@enabled
,@freq_type
,@freq_interval
,@freq_subday_type
,@freq_subday_interval
,@freq_recurrence_factor
,@active_start_date
,@active_end_date
,@active_start_time
,@active_end_time
END
PRINT char(13) + char(10);
PRINT N'COMMIT TRANSACTION'
PRINT N'GOTO EndSave'
PRINT N'QuitWithRollback:'
PRINT N' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'
PRINT N'EndSave:'
PRINT N'GO'
CLOSE schedulecursor;
DEALLOCATE schedulecursor;
GO
/*******************************************
Create Jobs
********************************************/
PRINT N'--Create Jobs'
PRINT char(13) + char(10)
PRINT N'DECLARE @ReturnCode INT';
PRINT N'SELECT @ReturnCode = 0';
DECLARE @job_name sysname
DECLARE @enabled int
DECLARE @notify_level_eventlog int
DECLARE @notify_level_email int
DECLARE @notify_level_netsend int
DECLARE @notify_level_page int
DECLARE @delete_level int
DECLARE @description nvarchar(512)
DECLARE @category_name sysname
Declare jobcursor CURSOR FAST_FORWARD FOR
SELECT sj.[name] jobname
, [enabled]
, [notify_level_eventlog]
, [notify_level_email]
, [notify_level_netsend]
, [notify_level_page]
, [delete_level]
, [description]
, sc.[name] categoryname
FROM [dbo].[sysjobs] sj
INNER JOIN [dbo].[syscategories] sc
ON sj.category_id = sc.category_id
OPEN jobcursor
PRINT N'BEGIN TRANSACTION';
PRINT char(13) + char(10);
FETCH NEXT FROM jobcursor
INTO @job_name
,@enabled
,@notify_level_eventlog
,@notify_level_email
,@notify_level_netsend
,@notify_level_page
,@delete_level
,@description
,@category_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT N'IF NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name= ' + char(39) + @job_name + char(39) + ')';
PRINT N'BEGIN'
PRINT N'EXEC @ReturnCode = sp_add_job';
PRINT N'@job_name = ' + '''' + cast(@job_name as nvarchar(max)) + '''' + ',';
PRINT N'@enabled = ' + cast(@enabled as nvarchar(max)) + ',';
IF @notify_level_eventlog > 0 PRINT N'@notify_level_eventlog= ' + cast(@notify_level_eventlog as nvarchar(max)) + ',';
--Handle email notification unsupported in sp_add_job
IF @notify_level_email > 0 AND @notify_email_operator_name IS NOT NULL PRINT N'@notify_level_email= ' + cast(@notify_level_email as nvarchar(max)) + ',';
IF @notify_level_email = 0 AND @notify_email_operator_name IS NOT NULL PRINT N'@notify_level_email= 1' + ',';
IF @notify_level_netsend > 0 PRINT N'@notify_level_netsend= ' + cast(@notify_level_netsend as nvarchar(max)) + ',';
IF @notify_level_page > 0 PRINT N'@notify_level_page= ' + cast(@notify_level_page as nvarchar(max)) + ',';
IF @delete_level > 0 PRINT N'@delete_level= ' + cast(@delete_level as nvarchar(max)) + ',';
IF @description IS NOT NULL PRINT N'@description= '+ char(39) + replace(cast(@description as nvarchar(max)),'''','')+ char(39) + ',';
IF @notify_email_operator_name IS NOT NULL PRINT N'@notify_email_operator_name= ' + char(39) + cast(@notify_email_operator_name as nvarchar(max)) + char(39) + ',';
PRINT N'@category_name= '+ char(39) + cast(@category_name as nvarchar(max))+ char(39) + ',';
PRINT N'@owner_login_name= ' + char(39) + cast(@owner_login_name as nvarchar(max)) + char(39);
PRINT char(13) + char(10);
PRINT N'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback';
PRINT N'END'
PRINT char(13) + char(10);
FETCH NEXT FROM jobcursor
INTO @job_name
,@enabled
,@notify_level_eventlog
,@notify_level_email
,@notify_level_netsend
,@notify_level_page
,@delete_level
,@description
,@category_name
END
PRINT char(13) + char(10);
PRINT N'COMMIT TRANSACTION'
PRINT N'GOTO EndSave'
PRINT N'QuitWithRollback:'
PRINT N' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'
PRINT N'EndSave:'
PRINT N'GO'
CLOSE jobcursor;
DEALLOCATE jobcursor;
GO
/*******************************************
Create Job Steps
********************************************/
PRINT N'--Create Jobs Steps'
PRINT char(13) + char(10)
PRINT N'DECLARE @ReturnCode INT';
PRINT N'SELECT @ReturnCode = 0';
DECLARE @job_name sysname
DECLARE @step_name sysname
DECLARE @step_id int
DECLARE @cmdexec_success_code int
DECLARE @on_success_action tinyint
DECLARE @on_success_step_id int
DECLARE @on_fail_action tinyint
DECLARE @on_fail_step_id int
DECLARE @retry_attempts int
DECLARE @retry_interval int
DECLARE @subsystem nvarchar(40)
DECLARE @command nvarchar(3200)
DECLARE @output_file_name nvarchar(200)
DECLARE @flags int
--DECLARE @server nvarchar(30)
--DECLARE @database_name sysname
--DECLARE @database_user_name sysname
Declare jobstepcursor CURSOR FAST_FORWARD FOR
SELECT sj.[name] jobname
, [step_name]
, [step_id]
, [cmdexec_success_code]
, [on_success_action]
, [on_success_step_id]
, [on_fail_action]
, [on_fail_step_id]
, [retry_attempts]
, [retry_interval]
, [subsystem]
, [command]
, [output_file_name]
, [flags]
--,[server]
--,[database_name]
--,[database_user_name]
FROM [dbo].[sysjobsteps] sjs
INNER JOIN [dbo].[sysjobs] sj
ON sjs.job_id = sj.job_id
ORDER BY sjs.job_id, sjs.step_id
OPEN jobstepcursor
PRINT N'BEGIN TRANSACTION';
PRINT char(13) + char(10);
FETCH NEXT FROM jobstepcursor
INTO @job_name,
@step_name,
@step_id,
@cmdexec_success_code,
@on_success_action,
@on_success_step_id,
@on_fail_action,
@on_fail_step_id,
@retry_attempts,
@retry_interval,
@subsystem,
@command,
@output_file_name,
@flags
--,
--@server,
--@database_name,
--@database_user_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT N'IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps sjs INNER JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id WHERE sj.[name] =' + char(39) + + cast(@job_name as nvarchar(max)) + + char(39) + ' and step_id = ' + cast(@step_id as nvarchar(max)) + ')';
PRINT N'BEGIN'
PRINT N'EXEC @ReturnCode = sp_add_jobstep';
PRINT N'@job_name = ' + char(39) + cast(@job_name as nvarchar(max)) + char(39) + ',';
PRINT N'@step_name = ' + char(39) + cast(@step_name as nvarchar(max)) + char(39) + ',';
PRINT N'@step_id= ' + cast(@step_id as nvarchar(max)) + ',';
PRINT N'@cmdexec_success_code= ' + cast(@cmdexec_success_code as nvarchar(max)) + ',';
PRINT N'@on_success_action= ' + cast(@on_success_action as nvarchar(max)) + ',';
PRINT N'@on_success_step_id= ' + cast(@on_success_step_id as nvarchar(max)) + ',';
PRINT N'@on_fail_action= ' + cast(@on_fail_action as nvarchar(max)) + ',';
PRINT N'@on_fail_step_id= ' + cast(@on_fail_step_id as nvarchar(max)) + ',';
PRINT N'@retry_attempts= ' + cast(@retry_attempts as nvarchar(max)) + ',';
PRINT N'@retry_interval= ' + cast(@retry_interval as nvarchar(max))+ ',';
PRINT N'@subsystem= ' + char(39) + cast(@subsystem as nvarchar(max)) + char(39)+ ',';
PRINT N'@command= ' + char(39) + replace( cast(@command as nvarchar(max)), char(39), char(39) + char(39)) + char(39)+ ',';
PRINT N'@output_file_name= ' + char(39) + cast(@output_file_name as nvarchar(max)) + char(39)+ ',';
PRINT N'@flags= ' + cast(@flags as nvarchar(max))
--+ ',';
--PRINT N'@server= ' + char(39) + cast(@server as nvarchar(max)) + char(39)+ ',';
--PRINT N'@database_name= ' + char(39) + cast(@database_name as nvarchar(max)) + char(39)+ ',';
--PRINT N'@database_user_name= ' + char(39) + cast(@database_user_name as nvarchar(max)) + char(39);
PRINT char(13) + char(10);
PRINT N'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback';
PRINT N'END'
PRINT char(13) + char(10);
FETCH NEXT FROM jobstepcursor
INTO @job_name,
@step_name,
@step_id,
@cmdexec_success_code,
@on_success_action,
@on_success_step_id,
@on_fail_action,
@on_fail_step_id,
@retry_attempts,
@retry_interval,
@subsystem,
@command,
@output_file_name,
@flags
--,
--@server,
--@database_name,
--@database_user_name
END
PRINT char(13) + char(10);
PRINT N'COMMIT TRANSACTION'
PRINT N'GOTO EndSave'
PRINT N'QuitWithRollback:'
PRINT N' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'
PRINT N'EndSave:'
PRINT N'GO'
CLOSE jobstepcursor;
DEALLOCATE jobstepcursor;
GO
/*******************************************
Attach schedules
********************************************/
PRINT N'--Attach schedules'
PRINT char(13) + char(10)
PRINT N'DECLARE @ReturnCode INT';
PRINT N'SELECT @ReturnCode = 0';
DECLARE @schedule_name sysname
DECLARE @job_name sysname
Declare scheduleattachcursor CURSOR FAST_FORWARD FOR
select ss.[name] schedule_name
, sj.name job_name
from msdb.dbo.sysschedules ss
inner join msdb.dbo.sysjobschedules sjs
ON ss.schedule_id = sjs.schedule_id
inner join msdb.dbo.sysjobs sj
ON sjs.job_id = sj.job_id
OPEN scheduleattachcursor
PRINT N'BEGIN TRANSACTION';
PRINT char(13) + char(10);
FETCH NEXT FROM scheduleattachcursor
INTO @schedule_name
,@job_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT N'BEGIN'
PRINT N'EXEC @ReturnCode = sp_attach_schedule';
PRINT N'@job_name = ' + '''' + cast(@job_name as nvarchar(max)) + '''' + ',';
PRINT N'@schedule_name = ' + '''' + cast(@schedule_name as nvarchar(max)) + '''' ;
PRINT N'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback';
PRINT N'END'
PRINT char(13) + char(10);
FETCH NEXT FROM scheduleattachcursor
INTO @schedule_name
,@job_name
END
PRINT char(13) + char(10);
PRINT N'COMMIT TRANSACTION'
PRINT N'GOTO EndSave'
PRINT N'QuitWithRollback:'
PRINT N' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'
PRINT N'EndSave:'
PRINT N'GO'
CLOSE scheduleattachcursor;
DEALLOCATE scheduleattachcursor;
GO
/*********************************************************
Set Job Server and Start Step
*********************************************************/
PRINT N'--Set Job Server and Start Step'
PRINT char(13) + char(10)
PRINT N'DECLARE @ReturnCode INT';
PRINT N'SELECT @ReturnCode = 0';
DECLARE @job_name sysname
DECLARE @start_step_id int
Declare jobcursor CURSOR FAST_FORWARD FOR
SELECT [name] jobname
, start_step_id
FROM [dbo].[sysjobs]
OPEN jobcursor
PRINT N'BEGIN TRANSACTION';
PRINT char(13) + char(10);
FETCH NEXT FROM jobcursor
INTO @job_name
,@start_step_id
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT N'IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name= ' + char(39) + @job_name + char(39) + ')';
PRINT N'BEGIN'
PRINT N'EXEC @ReturnCode = sp_update_job';
PRINT N'@job_name = ' + '''' + cast(@job_name as nvarchar(max)) + '''' + ',';
PRINT N'@start_step_id = ' + cast(@start_step_id as nvarchar(max));
PRINT char(13) + char(10);
PRINT N'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback';
PRINT N'END'
PRINT char(13) + char(10);
-- Add all jobs to local server (can be easily altered to support remote target servers if required)
PRINT N'IF NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs sj INNER JOIN msdb.dbo.sysjobservers sjs ON sj.job_id = sjs.job_id WHERE name= ' + char(39) + @job_name + char(39) + ')';
PRINT N'BEGIN'
PRINT N'EXEC @ReturnCode = sp_add_jobserver';
PRINT N'@job_name = ' + '''' + cast(@job_name as nvarchar(max)) + '''' + ',';
PRINT N' @server_name = N' + char(39) + '(local)' + char(39);
PRINT char(13) + char(10);
PRINT N'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback';
PRINT N'END'
PRINT char(13) + char(10);
FETCH NEXT FROM jobcursor
INTO @job_name
,@start_step_id
END
PRINT char(13) + char(10);
PRINT N'COMMIT TRANSACTION'
PRINT N'GOTO EndSave'
PRINT N'QuitWithRollback:'
PRINT N' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'
PRINT N'EndSave:'
PRINT N'GO'
CLOSE jobcursor;
DEALLOCATE jobcursor;
GO
參考
[SQL SERVER][Maintain]轉換不同SQL Server的登入和密碼
Migrating SQL Agent Jobs with Schedules using T-SQL
Availability Groups: How to sync logins between replicas
Synchronize Availability Group Logins and Jobs
automate the syncing of logins
Script and Migrate Agent Jobs between Servers using PowerShell
Synchronizing Read / Write SQL Agent Jobs Across AlwaysOn Replicas