"record to file" all step output; SQL Server Jobs

Add the yellow-highlighted line to your job create scripts for any steps where you'd like to "record to file" all step output; replace the green text with the name of the job. The other text will be replaced with the appropriate values from the server, job,  and calendar. Note the value of the @flags variable -- 4, meaning to log step output to the file as well.

.

.

.

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Compare & Update',

       @step_id=1,

       @cmdexec_success_code=0,

       @on_success_action=3,

       @on_success_step_id=0,

       @on_fail_action=2,

       @on_fail_step_id=0,

       @retry_attempts=0,

       @retry_interval=0,

       @os_run_priority=0, @subsystem=N'TSQL',

       @command=N'exec sf_refreshall ''SALESFORCE_PRD'',''Yes''',

       @database_name=N'SFDCSnapshot',

       @output_file_name=N'\\ Whatever \sqldba\JobLogs\$(ESCAPE_SQUOTE(INST))\Refresh_SFDCSnapshot_fr_PRD_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_NONE(STRTDT))_$(ESCAPE_NONE(STRTTM)).txt',

       @flags=4 -- include step output in log file

.

.

.

For the example above, if the step executed at 10am today, the log file name for this execution would be \\Whatever\sqldba\JobLogs\PRD5\Refresh_SFDCSnapshot_fr_PRD_1_20150908_100000.txt.