Install-DbaMaintenanceSolution job schedules incorrect

[Tisit]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Running Install-DbaMaintenanceSolution with auto scheduling and option HourlyLog creates daily schedule for "DatabaseBackup - USER_DATABASES - LOG".

Command to run:

Install-DbaMaintenanceSolution -InstallJobs -AutoScheduleJobs HourlyLog, DailyFull -SqlInstance localhost -ReplaceExisting

Also of note is that running the command without either DailyFull or WeeklyFull throws an error:

New-DbaAgentSchedule : Cannot bind argument to parameter 'SqlInstance' because it is null.
At D:\projekty\dbatools\public\Install-DbaMaintenanceSolution.ps1:524 char:54
+                 $fullschedule = New-DbaAgentSchedule @fullparams
+                                                      ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [New-DbaAgentSchedule], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,New-DbaAgentSchedule

And no schedule is created for "DatabaseBackup - USER_DATABASES - FULL". This is inconsistent with "DatabaseBackup - USER_DATABASES - DIFF", which in this case gets daily schedule

Steps to Reproduce

see above

Please confirm that you are running the most recent version of dbatools

2.0.4

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.19041.3031
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.19041.3031
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Doesn't matter

.NET Framework Version

does not matter

[rootMBX]

I have also experienced HourlyLog setting a schedule that runs once (daily) at 12:30 AM only. Non-recurring.

Workaround: Set schedule yourself -

"server1","server2" | Set-DbaAgentSchedule -Job "DatabaseBackup - USER_DATABASES - LOG" -ScheduleName "Hourly Log Backup" -Enabled -FrequencyType Daily -FrequencyInterval EveryDay -FrequencySubDayType Hours -FrequencySubdayInterval 1 -StartTime 003000 -EndTime 235959

[HCRitter]

I have reviewed the install-DbaMaintenanceSolution function, and I noticed that when selecting the "HourlyLog" option, it currently calls New-DbaAgentSchedule with the following parameters:

$logparams = @{
    SqlInstance       = $server
    Job               = "DatabaseBackup - USER_DATABASES - LOG"
    Schedule          = "Hourly Log Backup"
    FrequencyType     = "Daily"
    FrequencyInterval = 1
    StartTime         = "003000"
    Force             = $true
}

However, this configuration would create a scheduled task that executes the SQL Server job "DatabaseBackup - USER_DATABASES - LOG" every day at 3:00 AM on the specified SQL Server instance.

To achieve the intended hourly schedule, the correct parameters should be as follows:

$logparams = @{
    SqlInstance            = $server
    Job                    = "DatabaseBackup - USER_DATABASES - LOG"
    Schedule               = "Hourly Log Backup"
    FrequencyType          = "Daily"
    FrequencyInterval      = "1"
    FrequencySubdayType    = "Hours"
    FrequencySubdayInterval = 1
    StartTime              = "000000"
    Force                  = $true
}

With these parameters, the function will correctly create a schedule that runs the job every hour, starting at midnight. If others also notice this issue, I can create a commit with these changes.

[HCRitter]

@andreasjordan you would agree to this?

[andreasjordan]

Yes, I agree. But please use FrequencyInterval = 1 instead on FrequencyInterval = "1".

[andreasjordan]

Please also have a look at the issue around lines 502 to 524 if $AutoScheduleJobs includes neither WeeklyFull nor DailyFull.

[HCRitter]

I probably found the issue, 524 --> $fullschedule = New-DbaAgentSchedule @fullparams gets called, even when there is no Full parameter.

So replacing this:

                if ("WeeklyFull" -in $AutoScheduleJobs) {
                    $fullparams = @{
                        SqlInstance       = $server
                        Job               = "DatabaseBackup - USER_DATABASES - FULL"
                        Schedule          = "Weekly Full User Backup"
                        FrequencyType     = "Weekly"
                        FrequencyInterval = "Sunday" # 1
                        StartTime         = $start
                        Force             = $true
                    }
                } elseif ("DailyFull" -in $AutoScheduleJobs) {
                    $fullparams = @{
                        SqlInstance       = $server
                        Job               = "DatabaseBackup - USER_DATABASES - FULL"
                        Schedule          = "Daily Full User Backup"
                        FrequencyType     = "Daily"
                        FrequencyInterval = "EveryDay"
                        StartTime         = $start
                        Force             = $true
                    }
                }

                $fullschedule = New-DbaAgentSchedule @fullparams
                

with:

                if ("WeeklyFull" -in $AutoScheduleJobs -or "DailyFull" -in $AutoScheduleJobs) {
                    $fullparams = @{
                        SqlInstance       = $server
                        Job               = "DatabaseBackup - USER_DATABASES - FULL"
                        Schedule          = $null
                        FrequencyType     = $null
                        FrequencyInterval = $null
                        StartTime         = $start
                        Force             = $true
                    }
                
                    switch ($AutoScheduleJobs) {
                        "WeeklyFull" {
                            $fullparams.Schedule          = "Weekly Full User Backup"
                            $fullparams.FrequencyType     = "Weekly"
                            $fullparams.FrequencyInterval = "Sunday" # 1
                        }
                        "DailyFull" {
                            $fullparams.Schedule          = "Daily Full User Backup"
                            $fullparams.FrequencyType     = "Daily"
                            $fullparams.FrequencyInterval = "EveryDay"
                        }
                    }
                
                    $fullschedule = New-DbaAgentSchedule @fullparams
                }

This updated code ensures that $fullschedule is only created when either "WeeklyFull" or "DailyFull" is selected in $AutoScheduleJobs, eliminating the issue you observed.

[andreasjordan]

@potatoqualitee - As the code was introduced by you: Can you have a look at the proposed changes?