Click here to monitor SSC
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
Site Owners
Site Owners
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10081 Visits: 1
Comments posted to this topic are about the item create t-sql scripts for every jobs in Instance.
bugmenot-573553
bugmenot-573553
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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