Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

create t-sql scripts for every jobs in Instance. Expand / Collapse
Author
Message
Posted Sunday, September 30, 2007 10:10 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the item create t-sql scripts for every jobs in Instance.
Post #404712
Posted Friday, June 26, 2009 10:47 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 4:16 AM
Points: 71, Visits: 5,439
Doesnt seem to work with SQL2005? Breaks at referencing the "name" column of sysjobschedules since that column doesnt exist.
Post #742860
Posted Wednesday, August 12, 2009 3:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 2:16 PM
Points: 4, Visits: 224

I fixed the problem part....Just joined with another table....Please review it...It is ok now...

FROM msdb..sysjobschedules x, msdb..sysschedules y
WHERE x.schedule_id = y.schedule_id
and job_id = @jobID

Rabia


------------------------------------------------------------------------
-- Name : gera_jobs.sql
-- Author : Edu F Mont November 24, 2004
-- Mail : edu_dba@hotmail.com
-- RDBMS : SQL Server 2000
-- Desc : This SQL statement will provide script for backup all your jobs.
------------------------------------------------------------------------
-- IMPORTANT : Grow-up "maximum characters per column" (for 8192) and disable "print column headers" in 'tools' / 'options'
------------------------------------------------------------------------

SET NOCOUNT ON
GO

SET QUOTED_IDENTIFIER OFF
GO


declare @jobID nvarchar (40)
declare @cmd varchar (2000)


IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = '##jobids')
DROP TABLE ##jobids

SELECT @cmd = 'SELECT distinct (job_id) as job_id, 0 AS lido
INTO ##jobids
FROM msdb..sysjobs
ORDER BY 1'


EXEC (@cmd)


WHILE EXISTS (SELECT * FROM ##jobids WHERE lido = 0 )
BEGIN

SELECT @jobID = job_id
FROM ##jobids
WHERE lido = 0


SELECT
' -- DECLARACOES DE VARIAVEIS

BEGIN TRANSACTION
DECLARE @jobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'''+a.name+''') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'''+a.name+''''
FROM msdb..syscategories a, msdb..sysjobs b
WHERE a.category_id = b.category_id
and b.job_id = @jobID



SELECT
'-- Delete the job with the same name (if it exists)
SELECT @jobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'''+name+''')
IF (@jobID IS NOT NULL)
BEGIN

-- Check if the job is a multi-server job

IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN

-- There is, so abort the script

RAISERROR (N''Unable to import job '''''+name+''''' since there is already a multi-server job with this name.'', 16, 1)
GOTO QuitWithRollback
END
ELSE

-- Delete the [local] job

EXECUTE msdb.dbo.sp_delete_job @job_name = N'''+name+'''
SELECT @jobID = NULL
END
BEGIN'
FROM msdb..sysjobs
WHERE job_id = @jobID

IF (SELECT COUNT(*) FROM msdb..sysjobs WHERE job_id = @jobID and notify_email_operator_id <> 0) > 1
BEGIN
SELECT
'
-- Adiciona o job

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @jobID OUTPUT , @job_name = N'''+a.name+''', @owner_login_name = N'''+REPLACE (b.name,'dbo','sa')+''', @description = N'''+a.description+''', @category_name = N'''+c.name+''', @enabled = '+cast (a.enabled as nvarchar (4))+', @notify_level_email = '+cast (a.notify_level_email as nvarchar (4))+', @notify_level_page = '+cast (a.notify_level_page as nvarchar (4))+', @notify_level_netsend = '+cast (a.notify_level_netsend as nvarchar (4))+', @notify_level_eventlog = '+cast (a.notify_level_eventlog as nvarchar (4))+', @delete_level= '+cast (a.delete_level as nvarchar (4))+', @notify_email_operator_name = N'''+d.name+'''
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback '
FROM msdb..sysjobs a, master..syslogins b, msdb..syscategories c, msdb..sysoperators d
WHERE a.owner_sid = b.sid
and a.category_id = c.category_id
and d.id = a.notify_email_operator_id
and job_id = @jobID
END
ELSE
BEGIN
SELECT
'
-- Adiciona o job

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @jobID OUTPUT , @job_name = N'''+a.name+''', @owner_login_name = N'''+REPLACE (b.name,'dbo','sa')+''', @description = N'''+a.description+''', @category_name = N'''+c.name+''', @enabled = '+cast (a.enabled as nvarchar (4))+', @notify_level_email = '+cast (a.notify_level_email as nvarchar (4))+', @notify_level_page = '+cast (a.notify_level_page as nvarchar (4))+', @notify_level_netsend = '+cast (a.notify_level_netsend as nvarchar (4))+', @notify_level_eventlog = '+cast (a.notify_level_eventlog as nvarchar (4))+', @delete_level= '+cast (a.delete_level as nvarchar (4))+'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback '
FROM msdb..sysjobs a, master..syslogins b, msdb..syscategories c
WHERE a.owner_sid = b.sid
and a.category_id = c.category_id
and a.job_id = @jobID
END


SELECT
'
-- Adiciona o job steps

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = '+cast (a.step_id as nvarchar (3))+', @step_name = N'''+a.step_name+''', @command = N'''+REPLACE (a.command,"'","''")+''', @database_name = N'''+ISNULL (a.database_name,'') +''', @server = N'''', @database_user_name = N'''+ISNULL (a.database_user_name, '')+''', @subsystem = N'''+a.subsystem+''', @cmdexec_success_code = '+cast (a.cmdexec_success_code as nvarchar(2))+', @flags = '+cast (a.flags as nvarchar(2))+', @retry_attempts = '+cast (a.retry_attempts as nvarchar(2))+', @retry_interval = '+cast (a.retry_interval as nvarchar(2))+', @output_file_name = N'''+ISNULL (a.output_file_name, '')+''', @on_success_step_id = '+cast (a.on_success_step_id as nvarchar(3))+', @on_success_action = '+cast (a.on_success_action as nvarchar(2))+', @on_fail_step_id = '+cast (a.on_fail_step_id as nvarchar(2))+', @on_fail_action = '+cast (a.on_fail_action as nvarchar(2))+'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
FROM msdb..sysjobsteps a
WHERE a.job_id = @jobID




SELECT
'
-- Adiciona o job schedules

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'''+name+''', @enabled = '+cast (enabled as nvarchar (1))+', @freq_type = '+cast (freq_type as nvarchar (2))+', @active_start_date = '+cast (active_start_date as nvarchar(10))+', @active_start_time = '+cast (active_start_time as nvarchar(10))+', @freq_interval = '+cast (freq_interval as nvarchar(3))+', @freq_subday_type = '+cast (freq_subday_type as nvarchar(5))+', @freq_subday_interval = '+cast (freq_subday_interval as nvarchar(10))+', @freq_relative_interval = '+cast (freq_relative_interval as nvarchar(5))+', @freq_recurrence_factor = '+cast (freq_recurrence_factor as nvarchar(10))+', @active_end_date = '+cast (active_end_date as nvarchar(10))+', @active_end_time = '+cast (active_end_time as nvarchar(10))+'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback '
--FROM msdb..sysjobschedules
--WHERE job_id = @jobID
FROM msdb..sysjobschedules x, msdb..sysschedules y
WHERE x.schedule_id = y.schedule_id
and job_id = @jobID

PRINT '
-- Adiciona o Target Servers

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N''(local)''
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO'



UPDATE ##jobids
SET lido = 1
WHERE job_id = @jobID

END

SET NOCOUNT OFF
GO

SET QUOTED_IDENTIFIER ON
GO


Post #769706
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse