Modify Backup Directory Path Ola Maintenance Script

Question

I have configured backup using SQL Server Maintenance Solution by Ola (https://ola.hallengren.com).

In the backup job, I specified BackupDirectory as a shared folder (eg: \comp1\path). It worked fine, but now I need to change the path. Assuming that I don't want to modify/recreate the jobs, is there a way to modify the path inside the sql jobs?

Yes. Check the backup jobs, there will be a parameter for backup path. Just change it to what you want and you are done. You Just have to make sure SQL Agent account has write access to new backup path. That's it. — SQLPRODDBA, May 19 '16 at 09:52
Yes, I am aware of it. What I meant is to change that all in one go. As for now there are a few jobs (eg: backup full, backup dif, backup log,etc). I am wondering how to change all of that without doing it one by one. — Syakur Rahman, May 19 '16 at 10:31

John K. N. · Accepted Answer · 2016-05-20T06:35:43.350

You can search the command column of the sysjobsteps table for the path you have in your current jobs with the following statement:

SELECT * FROM msdb.dbo.sysjobsteps AS s WHERE s.command LIKE '%\\comp1\path%'

You should then be able to update the sysjobsteps with the following statement:

USE msdb
GO
UPDATE sysjobsteps SET command = REPLACE(command, '<your_old_path_here>', '<your_new_path_here>')

You have to be careful to run the SELECT first to check if the correct commands are being returned. You could also backup the msdb before you start as a precaution.

To be on the safer side, you can right click a job and "Script Job As | Drop and Create To | New Query Editor Window" and then modify the path and execute.

Be sure to remove the line containing @schedule_uid=N'<GUID>' in the sp_add_jobschedule part before you attempt to recreate the job.

+1 on the "Script Job As | Drop and Create To | New Query Editor Window" technique. And I found only 4 jobs where this needed to be changed, each job with the name "DatabaseBackup" so its pretty simple and safe to make the changes, drop and recreate without a somewhat risky UPDATE statement. — Jeff Mergler, Jan 07 '20 at 01:27

Marcello Miorelli · Answer 2 · 2019-05-24T18:21:49.013

Just to complement the great answer by ho2use

I use this query below to find out where the backups are currently going to:

USE [msdb]
GO
SELECT  j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled 
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON  js.job_id = j.job_id 
JOIN    master.dbo.sysservers s
    ON  s.srvid = j.originating_server_id
WHERE   js.command LIKE N'%BACKUP%'

so in my case it will be:

EXECUTE [dbo].[DatabaseBackup]  
@Databases = 'USER_DATABASES', 
@Directory = N'\\homer\FullBackups\',  
@BackupType = 'FULL',  
@Verify = 'Y',  
@CleanupTime = 72,  
@CheckSum = 'Y',  
@LogToTable = 'Y'

then doing the replacement:

USE msdb
GO
--do a backup of the table before the update
SELECT T.*  
INTO dbo._BACKUP_OF_sysjobsteps_184413_4 
FROM [dbo].[sysjobsteps] T  

BEGIN TRAN T1
SELECT @@TRANCOUNT

UPDATE sysjobsteps SET command = REPLACE(command, '\\homer\FullBackups\', '\\homer\sys_vol\TS-SQLBackups')
--(14 rows affected)

COMMIT TRAN T1
SELECT @@TRANCOUNT

Now that the jobs point to the new folder, you must grant the new folder all the permissions that were previously granted to the old folder.

I have a powershell script that I often use for doing this.

this is to be run in powershell:

$Acl = Get-Acl "\homer\FullBackups" Set-Acl "\homer\sys_vol\TS-SQLBackups" $Acl

you might need to double check manually if the folder exists, and if you have all the required permissions

after that you could (on an appropriate time) check the full backup job see if it runs successfully to the end

exec msdb.dbo.sp_start_job @job_name = 'DatabaseBackup - USER_DATABASES - FULL'

Modify Backup Directory Path Ola Maintenance Script

2 Answers2