This SQL statement will provide script for backup

------------------------------------------------------------------------
--  Name   :  gera_jobs.sql  
--  Author :  Edu F Mont November 24, 2004
--  RDBMS  :  SQL Server 2000 
--  Desc   :  This SQL statement will provide script for backup all your jobs.
--  Remark :  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




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