In the last week of February, I think I will try to do some smaller posts every week with something new I learned or found useful while conducting my job. The goal will be to submit small tidbits of info that will provide some sort of value for those seeking it! This week, it is going to be about changing your default backup path for your database maintenance jobs!
There will come a time where you might need to change your default path for your backup jobs. For some of you, this might be a pretty straightforward task on a handful of servers.For the rest of us, it is not ideal to hop from one instance to the next, updating the default paths manually across several servers. Thankfully for everyone, our community has a solution (most of the time) for our daily struggles. And this situation is no different!
Identifying WHERE your backups are going
I know you are probably thinking, “How could ANYONE not know where your backups are going?” I will simply respond by saying please see any sysadmin that was dubbed an accidental DBA by their company and told to “just figure it out.” Plus, maybe you are someone new to the gig and really don’t have any guidance to where anything is! These things happen people!
A quick solution would be to connect to your database servers and run the following command:
USE msdb; 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%'
This will look through system databases, identify commands where a backup path is mentioned, and output the value to you. From this info, you can identify what the job id is, servername, name of job, what step the backup command is in, and then the command itself. Unfortunately, I do not have a test server to show you the output of the command, so you will need to just trust me (or run it on your server!).
Change the backup path!
Now, the next step, with the use of the following script, makes changing default backup paths an absolute BREEZE.
USE msdb;
DECLARE @Find nvarchar(max);
DECLARE @Replace nvarchar(max);
DECLARE @DebugOnly bit;
SET @Find = N'\\SERVERA\Backups';
SET @Replace = N'\\SERVERB\Backups';
SET @DebugOnly = 1;
IF OBJECT_ID(N'tempdb..#excludeJobs', N'U') IS NOT NULL
BEGIN
DROP TABLE #excludeJobs;
END
CREATE TABLE #excludeJobs
(
JobName sysname NOT NULL
PRIMARY KEY CLUSTERED
);
INSERT INTO #excludeJobs (JobName)
VALUES ('The Name of a job you want to skip');
IF OBJECT_ID(N'tempdb..#deets', N'U') IS NOT NULL
DROP TABLE #deets;
CREATE TABLE #deets
(
JobName sysname NOT NULL
, StepName sysname NOT NULL
, OldCommand nvarchar(max) NOT NULL
, NewCommand nvarchar(max) NOT NULL
, PRIMARY KEY (JobName, StepName)
);
DECLARE @JobName sysname;
DECLARE @StepName sysname;
DECLARE @StepID int;
DECLARE @Command nvarchar(max);
DECLARE @NewCommand nvarchar(max);
BEGIN TRY
BEGIN TRANSACTION;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT sj.name
, sjs.step_name
, sjs.step_id
, sjs.command
FROM dbo.sysjobsteps sjs
INNER JOIN dbo.sysjobs sj ON sjs.job_id = sj.job_id
WHERE sjs.command LIKE N'%' + @Find + N'%' ESCAPE N'|' COLLATE SQL_Latin1_General_CP1_CI_AS
AND sj.enabled = 1
AND NOT EXISTS (
SELECT 1
FROM #excludeJobs ej
WHERE ej.JobName = sj.name
)
ORDER BY sj.name
, sjs.step_name;
OPEN cur;
FETCH NEXT FROM cur INTO @JobName
, @StepName
, @StepID
, @Command;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @NewCommand = REPLACE(@Command, @Find, @Replace) COLLATE SQL_Latin1_General_CP1_CI_AS;
INSERT INTO #deets (JobName, StepName, OldCommand, NewCommand)
SELECT JobName = @JobName
, StepName = @StepName
, PriorCommand = @Command
, NewCommand = @NewCommand;
IF @DebugOnly = 0
BEGIN
EXEC dbo.sp_update_jobstep @job_name = @JobName, @step_id = @StepID, @command = @NewCommand;
PRINT N'Updated ' + @JobName;
END
FETCH NEXT FROM cur INTO @JobName
, @StepName
, @StepID
, @Command;
END
CLOSE cur;
DEALLOCATE cur;
SELECT *
FROM #deets;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
PRINT N'Transaction rolled back';
END
PRINT ERROR_MESSAGE();
PRINT ERROR_LINE();
END CATCH
Lets focus on the parameters you can modify, which make this script super helpful. In the “SET @Find” parameter, insert the backup path you have already identified either on your own or via the script I provided before. Next, you will want to add in the “SET @REPLACE” parameter the NEW default path you would like to update to all of your backup jobs. Finally, and this is my favorite part, is the “SET @debugOnly” parameter. By default, I leave this set to 1 as it will show you the output of running the script without actually changing the default path. Once you have set your parameters, run the script and take a look at the details provided. In the results, you can see the JobName, StepName, OldCommand, NewCommand, OldOutputfile, and NewOutputfile. By running the script in debug mode, you can verify if your new paths are correct. Once you are confident in the new path, set the debug mode to 0, and execute the script. I recommend running the script twice, as the second execution should come up blank signifying that your change was made successfully.
Do the backup jobs run successfully with the new path?
Since I cannot account for every situation, I will speak plainly. I strongly encourage that you run a system database backup just to verify that the backups can write to the new path. The last thing you want to do is make changes to the backup paths, and then fail to verify if the database server can even write to the new path. My guess is you will receive an unwanted call when the database server starts sending emails out saying backups are failing….
The script I provided above was not my own to be fair, and you can find the author’s write up here
Find-and-Replace for SQL Server Agent Jobs – SQL Server Science
Until next week!