SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


create t-sql scripts for every jobs in Instance.


create t-sql scripts for every jobs in Instance.

Author
Message
bugmenot-573553
bugmenot-573553
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 5439
Doesnt seem to work with SQL2005? Breaks at referencing the "name" column of sysjobschedules since that column doesnt exist.
Rabia Cinar Yuksel
Rabia Cinar Yuksel
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 240
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search