demosql45.sql

--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