Disabling backup Jobs

  • I've not done a lot with disabling jobs with script, I need to read the job names from a text a disable them or enable them on all servers within the environment? Is there any good examples for doing this?

  • EXEC msdb.dbo.sp_update_job @job_name='<your_job_name>', @enabled = 0 /* to disable, or 1 to enable */

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • I see that one but need to put the names of jobs in a text file, cause that will enable and disable all jobs, some jobs I don't want to disable, so instead of hard coding them I want to put them in a text file so if a new job is added it reads from the text file, and similar if it is removed.

  • Use DBATools and the Set-DbaAgentJob function.

    You can easily read in the text file as an array of jobs to pass to the function to enable of disable en-mass

     

    $JobsToChange = Get-Content c:\temp\joblist.txt

    Set-DbaAgentJob -SqlInstance <Servername> -Job $JobsToChange -Disable

     

  • FWIW, whenever we disable a job, we leave a trace of it by renaming the job ! (we add a prefix e.g. 'DBA-MaintenanceWindow-'

    Doing so, we know who/what did disable the job, and we prevent "sp_start_job" execution in case an application might try to start it directly (which we normally do NOT allow).

    As a second note: Be careful when you are using alerts to start jobs!

    If you fire an alert for a disabled job, SQLAgent will keep on trying to launch the job ( 6 times/sec ) and will write to SQLAGENT.OUT whenever it attempts to do so, until the disk is full !

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Cant use those PS features, not allowed to install on servers

  • cbrammer1219 wrote:

    I see that one but need to put the names of jobs in a text file, cause that will enable and disable all jobs, some jobs I don't want to disable, so instead of hard coding them I want to put them in a text file so if a new job is added it reads from the text file, and similar if it is removed.

    Put the job names in a SQL table. Read the table and run dynamic SQL with each record from the table.

    You could have a 2nd column for Enable/Disable, so your script will perform either function.

  • I wish I could use a table but can't create tables on the servers, I have to read the Job name from a text file, which the jobs are the same across an environment, app server, web server and db server so I have to (disable/enable) those jobs across all those server normally I would just create a package but that option isn't available. I may not be explaining properly hope there's not confusion. I need to use 2016 SSMS to create a manually executable job, that reads a text file, and enables or disables a job name within the text file, there could be 1, 10, 20 ,30 entries in the text file across an environment, that could have 4 server for the environment or 30..

  • Maybe you can BCP from a TXT file into a Temp table to start the process ?

    Otherwise, with your limitations, maybe copy-paste saved scripts into SSMS. How often does this need to run ?

    • This reply was modified 1 week, 2 days ago by  homebrew01.
    • This reply was modified 1 week, 2 days ago by  homebrew01.
  • It is manual, so when deployments are done, I would run it to disable them, then once deployment is done, I would run it to re-enable those disabled jobs in the text file.

     

  • Here's what I have come up with, but I get an error.

    Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 25 [Batch Start Line 2]

    Supply either @job_id or @job_name to identify the job. Is there a better way of doing this? and why would I be getting this error, I am supplying the job_name

    USE MSDB;

    GO

    DECLARE @job_name nvarchar(max)

    DECLARE @enable int

    create table #JobsToDisableEnable

    (

    name nvarchar(max),

    enable int

    )

    DECLARE @Query nvarchar(max)

    SET @Query ='BULK INSERT #JobsToDisableEnable FROM "G:\SQL_Jobs\DisableEnableJobs\joblist.txt"

    WITH(

    ROWTERMINATOR = ''\n'',

    FIRSTROW = 2

    )'

    EXEC (@Query)

    DECLARE job_cursor CURSOR READ_ONLY FOR

    SELECT @job_name,[enable]

    FROM #JobsToDisableEnable

     

     

    OPEN job_cursor

    FETCH NEXT FROM job_cursor INTO @job_name,@enable

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC msdb.dbo.sp_update_job @job_name = @job_name, @enabled = 1

    FETCH NEXT FROM job_cursor INTO @job_name, @enable

    END

    CLOSE job_cursor

    DEALLOCATE job_cursor

  • The DECLARE for the cursor is incorrect: you need to remove the @ from before "@job_name":

    DECLARE job_cursor CURSOR READ_ONLY FOR
    SELECT job_name,[enable] --<<--
    FROM #JobsToDisableEnable

    Also, in the EXEC, you should be using the value from the table not hard-coding 1:

    EXEC msdb.dbo.sp_update_job @job_name = @job_name, @enabled = @enable --<<--

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • I noticed that it is using the value from sp_update_job, how would i get it to use the values in the FROM #JobsToDisableEnable? Like its doing enabling all and disabling all, where I just want it to enable or disable on entries within the text file.

    • This reply was modified 5 days, 20 hours ago by  cbrammer1219.
  • You have the general structure for that, you just had a typo in the SELECT for the cursor.  You had:

    DECLARE job_cursor CURSOR READ_ONLY FOR

    SELECT @job_name, [enable]

    FROM #JobsToDisableEnable

    @job_name will contain NULL by default, so you were SELECTing NULL, and using that in the EXEC statement for the job, which is why you got the error about "Supply either @job_id or @job_name to identify the job".

    If you correct the cursor to SELECT from the data you read in, you should be OK.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Here is what I changed it to, but still getting that error.

    USE MSDB;

    GO

    DECLARE @job_name nvarchar(max)

    DECLARE @enable int

    create table #JobsToDisableEnable

    (

    job_name nvarchar(max),

    enable int

    )

    DECLARE @Query varchar(1000)

    SET @Query ='BULK INSERT #JobsToDisableEnable FROM "G:\SQL_Jobs\DisableEnableJobs\joblist.txt"

    WITH(

    ROWTERMINATOR = ''\n'',

    FIRSTROW = 0

    )'

    EXEC (@Query)

    DECLARE job_cursor CURSOR READ_ONLY FOR

    SELECT @job_name,[enable]

    FROM #JobsToDisableEnable

    OPEN job_cursor

    FETCH NEXT FROM job_cursor INTO @job_name,@enable

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC msdb.dbo.sp_update_job @job_name = @job_name, @enabled = @enable

    FETCH NEXT FROM job_cursor INTO @job_name, @enable

    END

    CLOSE job_cursor

    DEALLOCATE job_cursor

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply