SQL Server Logging – Specify Different Log File Locations with Hallengren Scripts

ola-hallengrensql server

I appreciate Ola Hallengren scripts. Being new to SQL Server, they've made my life so much easier. Thank you Mr. Hallengren.

I have 5 user databases that I need to back up at separate times, so instead of using USER_DATABASES, I'm providing the database name. When I created the stored procedures with the script, I specified the Output File Directory as something like G:\Logs\Backups. The log files are there, but it would be great to either have the database name in the logfile name, or pass the logfile path as a parameter.

I'm also logging to dbo.CommandLog. It would be nice to identify the log file that I'm looking for without looking inside.

Is there a way to do this, without changing maintenance_solution.sql?

I don't like the idea of modifying because when an update comes out, those would be gone. To me, the scripts are quite complicated, as I'm coming up-to-speed with MS SQL and T-SQL.

Any insights are appreciated.

Sherrie

Best Answer

Assuming you have separate jobs to backup each database, your best bet is to edit that manually.

NUTS

Unfortunately, the way these tokens are implemented, you can only get the database name when it's raised in an alert.

All of the tokens that start with "A-" can't be called the way general information tokens can.

Token   Description
(A-DBN) Database name. If the job is run by an alert, the database name value automatically replaces this token in the job step.
(A-SVR) Server name. If the job is run by an alert, the server name value automatically replaces this token in the job step.
(A-ERR) Error number. If the job is run by an alert, the error number value automatically replaces this token in the job step.
(A-SEV) Error severity. If the job is run by an alert, the error severity value automatically replaces this token in the job step.
(A-MSG) Message text. If the job is run by an alert, the message text value automatically replaces this token in the job step.

If they could, I'm sure they'd end up in Ola's code here:

  BEGIN
    SET @TokenServer = '$' + '(SRVR)'
    SET @TokenJobID = '$' + '(JOBID)'
    SET @TokenStepID = '$' + '(STEPID)'
    SET @TokenDate = '$' + '(STRTDT)'
    SET @TokenTime = '$' + '(STRTTM)'
  END

If you try to tack A-DBN on to the string, the job will fail.

$(ESCAPE_SQUOTE(SQLLOGDIR))\DatabaseBackup_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM))_$(ESCAPE_SQUOTE(A-DBN)).txt

Message Unable to start execution of step 1 (reason: Variable A-DBN not found). The step failed.

Hope this helps!

Related Question