Select and Start a SQL Job without knowing the full job name or history

So I was trying to figure out how to get the TLog backup job’s name based on Back History because in this case, the job name happens to vary between SQL Server Instances. Sounds simple enough (well…not quite), then I realized for new SQL Server instances this job might not have even ever ran…so there went that idea.

Instead, I just used dynamic SQL, get the job name and do the work. Here’s the code.

USE MSDB;

DECLARE @TlogJobName nvarchar(max)

SELECT top 1 @TlogJobName = j.name
FROM MSDB.dbo.sysjobs j
INNER JOIN MSDB.dbo.sysjobsteps s ON j.job_id = s.job_id
WHERE j.name LIKE 'Tran%' --Commonly 'Transaction Log Maintenance Plan.Subplan_1'

PRINT 'Starting Sql Job: '+@TlogJobName

EXEC msdb.dbo.sp_start_Job @job_name = @TlogJobName;
WAITFOR DELAY '00:01:00' --Wait 1 minute for job to finish.

/*Check job histry should be done by now.*/
SELECT  j.name AS 'JobName',
msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime',
h.run_status --(1=SUCCESS)
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id
WHERE j.enabled = 1
AND j.name = @TlogJobName
AND s.step_id = h.step_id
AND h.step_id <> 0
ORDER BY JobName, RunDateTime DESC

Hope you find it useful as well.

Thanks,
Hiram