Trouble automating the install/execution of Ola Hallengren 'SQL Server Maintenance Solution'

Question

I'll do my best to explain this as best I can, but kindly bear with me as I'm not a DBA and hacking my way through this. I'm tasked with ongoing deployment of SQL Server for end user clients on new servers. I have a PowerShell script that automates much of this including implementing all of the backup and maintenance agent jobs (pre-configured with schedules and required settings). Working largely with SQL WEB edition, I don't have the option to import maintenance packages, so as a work-around I've exported my pre-configured jobs and I import them as *.sql files as part of the deployment. This one PowerShell script handles the provisioning of SQL Server 2012, 2014, 2016 and 2017 (depending on what the client wants).

My specific issue is that the root folder for SQL Server we use isn't consistent due to different drive letters used and versions of SQL Server, so I'm unable to get jobs that rely on the @BackupDirectory directory variable to work as this can change from server to server. What I'm trying to achieve is setting the @BackupDirectory variable dynamically based on the install path at run time within the maintenance jobs. I only have it partially working.

To get the install path, I'm using something like this (borrowed/modified from other searches):

declare @rc int, @dir nvarchar(4000) 
DECLARE @BackupPath nvarchar(max)

exec @rc = master.dbo.xp_instance_regread
      N'HKEY_LOCAL_MACHINE',
      N'Software\Microsoft\MSSQLServer\Setup',
      N'SQLPath', 
      @dir output, 'no_output'

set @BackupPath = @dir+'\Backup'

Which returns something like this:

F:\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup

I'm trying to add this same logic to the SQL Server Agent job named DatabaseBackup - USER_DATABASES - FULL, however the problem I'm running into is that I don't know how to call a variable within this command:

@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''SYSTEM_DATABASES,USER_DATABASES'', @Directory = N''F:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup'', @BackupType = ''FULL'', @Verify = ''Y'', @CleanupTime = 120, @CheckSum = ''Y'', @LogToTable = ''Y''" -b',  

I need the @Directory = equal to my variable of @BackupPath. If I try something simply like this I get an error about:

Must declare the scalar variable @BackupPath

@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''SYSTEM_DATABASES,USER_DATABASES'', @Directory = @BackupPath, @BackupType = ''FULL'', @Verify = ''Y'', @CleanupTime = 120, @CheckSum = ''Y'', @LogToTable = ''Y''" -b', 

I appreciate that the way I'm going about doing this might be convoluted and there's an easier path, so I'm open to suggestions, but there's constraints that have led me here. Unfortunately, I don't know T-SQL well, so I've significantly searched and tried as best I could, but remain stuck. Our overall goal is to keep these installations as uniform as possible, so I'd like to keep the backup directory within the normal root directory install path.

Hopefully I've explained this clearly! I greatly appreciate any help and input.

Additional information

I'm adding the full T-SQL of the Agent Job so you can see it, minus my trying to add the variable. You'll only see the path that's normally added when the main script creates the job.

USE [msdb]
GO

/* ********* Get Install Directory for Backup folder path ********* */

declare @rc int, @dir nvarchar(4000) 
DECLARE @BackupPath nvarchar(max)

exec @rc = master.dbo.xp_instance_regread
      N'HKEY_LOCAL_MACHINE',
      N'Software\Microsoft\MSSQLServer\Setup',
      N'SQLPath', 
      @dir output, 'no_output'
set @BackupPath = @dir+'\Backup'

/****** Object:  Job [DatabaseBackup - Daily FULL Backup]    Script Date: 3/5/2018 4:28:49 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 3/5/2018 4:28:49 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DatabaseBackup - Daily FULL Backup', 
        @enabled=1, 
        @notify_level_eventlog=2, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'Source: https://ola.hallengren.com', 
        @category_name=N'Database Maintenance', 
        @owner_login_name=N'NT SERVICE\SQLSERVERAGENT', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [DatabaseBackup - ALL - FULL]    Script Date: 3/5/2018 4:28:49 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DatabaseBackup - ALL - FULL', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @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'CmdExec', 
        @command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''SYSTEM_DATABASES,USER_DATABASES'', @Directory = N''F:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup'', @BackupType = ''FULL'', @Verify = ''Y'', @CleanupTime = 120, @CheckSum = ''Y'', @LogToTable = ''Y''" -b', 
        @output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\DatabaseBackup_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily Full Backup', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20180305, 
        @active_end_date=99991231, 
        @active_start_time=50000, 
        @active_end_time=235959, 
        @schedule_uid=N'169d88c5-5d47-439b-b576-389e9395b9c6'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Further considerations

Thinking about it, I have a work around where I can perform this outside of SQL Server by querying the registry with PS, then modifying the .sql input file for this job. I'd like to see if it's possible to do solely within SQL Server though and learn from it.

Answer 1

Passing a variable to that SQL Agent step in that manner is not possible considering it does not know about the variable itself. A variable in T-SQL has to be defined/declared before it can be used...hence the error that it is not declared. Your further consideration is actually the proper way to do that with PowerShell. You would include looking up that value via PowerShell and then insert it into the T-SQL definition of the SQL Agent job.

Now, an alternative that I would just note (because it is built around folks that are not day-to-day DBAs) is dbatools PowerShell module includes commands that are "Ola-aware". We have a command that you can use to deploy the complete Ola maintenance solution.

You would use Install-DbaMaintenanceSolution to deploy the jobs, and it includes a few parameters that allow you to customize the cleanup time, backup directory, etc.

You can also utilize a few other commands to manipulate job schedules (Set-DbaAgentSchedule) according to your needs, or even based on a group of servers.

SMO is more easily accessible in the module so grabbing thinks like the backup directory or the root instance directory can be done with a few lines:

Import-Module dbatools
$server = Connect-DbaInstance -SqlInstance MyServer
# Grab the default backup directory (if it is set)
$server.BackupDirectory
# Grab the root directory of the instance
$server.RootDirectory

Answer 2

I too experienced the same issue and I used running powershell with sqlcmd with input variable as backup path. SQLcmd tool has an option to pass the input parameter as a variable

Answer 3

I was able to pass variable values to backup job with Powershell and @ strings, eg.

$localname = $env:COMPUTERNAME

$folderPath = "\\network_share\backup\$localname"

$BackupFullQuery = @"
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = '$folderPath',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 360,
@CheckSum = 'Y',
@LogToTable = 'Y'
"@

Invoke-SqlCmd -ServerInstance $SQLServer -Query $BackupFullQuery