I have a server where all my sql server backups are currently being saved to the G: drive.

Now there is no enough disk space on drive G: to keep backups for the last 5 days. I have asked to add another 100 GB to this G: drive but that was not possible, so they gave me another drive instead, the I: drive with extra disk space, as you can see on the picture below.

enter image description here

Now I need to replace all the references to the G: drive in the sql server jobs below to point to the new I: drive.

enter image description here

Suppose I have 3 servers in the same situation, how can I achieve this by T-SQL?

up vote 3 down vote accepted

You could try this cursor approach to read rows from dbo.sysjobsteps and execute sp_update_jobstep after doing a search/replace for the string you want to change.

This could also be improved to ONLY update job steps that actually have the string.

DECLARE @cmd VARCHAR(max)
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @step_id INT
DECLARE @command NVARCHAR(max)

DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT job_id
    ,step_id
    ,command
FROM msdb.dbo.sysjobsteps

OPEN _CURSOR

FETCH NEXT
FROM _CURSOR
INTO @job_id
    ,@step_id
    ,@command

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @command = replace(@command, 'g:', 'I:')

    EXEC msdb.dbo.sp_update_jobstep @job_id = @job_id
        ,@step_id = @step_id
        ,@Command = @command

    FETCH NEXT
    FROM _CURSOR
    INTO @job_id
        ,@step_id
        ,@command
END --End While

CLOSE _CURSOR

DEALLOCATE _CURSOR

For completeness (since from your screenshot, you are using Ola's backup solution), the newer version of Ola's script has the ability to specify directory structure

Default directory structure: {ServerName}${InstanceName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}{Partial}{CopyOnly}

The issue - DatabaseBackup - configurable folder names is closed.

You could update msdb..sysjobsteps table:

UPDATE s
SET command = Replace(command, 'G:\', 'I:\')
FROM msdb..sysjobsteps s
    JOIN msdb..sysjobs o ON s.job_id = o.job_id
WHERE o.[name] LIKE 'DatabaseBackup%'

Another one to script the jobs you required and alter the drive location which can be done through SSMS. Click on jobs in sql server agent.

enter image description here

Then click view object explorer details or press F7. enter image description here

Once you are in that window do a ctrl + left click for the required jobs

and select script jobs then click on drop and create. enter image description here

This will create a drop and create scripts for all the jobs in a single script. Once this is generated you can review it and change the drive location to new one manually or do a Find and Replace in SSMS.
Run the script so new jobs are created with the required params.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.