Recently, I had to move backups to a different drive ,so, I copied all the existing backups to the new location but the problem is with our SQL backup jobs. Our backups are scheduled using SQL Server Agent and the backup path is hard coded in the job step. There are 100’s of jobs across multiple servers ,so, I had to edit each one of them and replace the path with the new one. This solution would take forever and so, i thought of doing it in a better way such as using a script.
Below is the powershell code which basically loops through the backup jobs in the text file(I saved the backup job names to a text file) and scripts the backup job step to a text file(backup job has multiple steps but step 1 has backup location hard coded in it) and drops that step from the job. Now rerun the script that is saved into the file after replacing the backup location with the new one.
$Module = Get-Module | where-object {$_.Name -like ‘SQLPS’} | Select-Object Name
IF ($Module -ne “SQLPS”)
{
Import-Module SQLPS
}
$BackupJobs=Get-Content "C:\Users\SQLWhisper\Documents\BackupJobs.Txt"
Foreach ($BackupJob in $BackupJobs)
{
$a =new-object microsoft.sqlserver.management.smo.server "ServerName\InstanceName"
$b=$a.JobServer.Jobs|where-Object {$_.Name -like $BackupJob}
$c=$b.JobSteps|where-object {$_.ID -like "1"}
$C.Script() |Out-File "C:\Users\SQLWhisper\Documents\BackupJobs-Step1.txt" -append
$C.Drop()
}
Remove-Module SQLPS
}
SQL Server Repository 