Public/New-sqmBackupMaintenanceJob.ps1
|
<#
.SYNOPSIS Creates a SQL Agent job with two steps that implement the full dynamic backup maintenance workflow. .DESCRIPTION Creates a single SQL Agent job containing two PowerShell steps: Step 1 — Sync-BackupExcludeTable Calls Sync-sqmBackupExcludeTable to synchronise master.dbo.sqm_BackupExclude with the current set of databases on the instance. This ensures the exclude table is up-to-date before the actual backup starts. Step 2 — Backup-UserDatabases-<BackupType> Calls Invoke-sqmUserDatabaseBackup with -All and all configured options (UseExcludeTable, CheckPreferredReplica, MailTo, MailProfile, MailOnSuccess, BackupPath, CleanupTime). Both steps use the PowerShell subsystem so that the sqmSQLTool module is imported fresh at each execution. This means the job is fully self-contained and does not depend on the SQL Server Agent service account's PowerShell profile. Default schedule per backup type (applied to -ScheduleDays/-ScheduleTime/-ScheduleIntervalMinutes whenever the respective parameter is not explicitly specified): FULL — every day (@('EveryDay')) at 20:15, once DIFF — Monday-Saturday (@('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday')) at 20:00, once LOG — every day (@('EveryDay')), starting 00:00, every 15 minutes Default cleanup retention per backup type (applied via -CleanupTime unless overridden, skipped entirely with -NoCleanup): FULL 4 weeks ('4w'), DIFF 2 weeks ('2w'), LOG 48 hours ('48h'). Old backup files are removed by Invoke-sqmUserDatabaseBackup (Remove-DbaBackup) after each run, matching only that BackupType's own file extension (.bak for FULL/DIFF, .trn for LOG). .PARAMETER SqlInstance SQL Server instance. Default: current computer name ($env:COMPUTERNAME). .PARAMETER SqlCredential PSCredential for the SQL connection. .PARAMETER JobName Name of the SQL Agent job to create. When not specified, the name is read from the module configuration depending on -BackupType (Set-sqmConfig -BackupMaintenanceJobNameFull/ -Diff/-Log); if that isn't configured either, defaults to 'sqm-BackupMaintenance-<BackupType>'. .PARAMETER BackupType Backup type: 'FULL', 'DIFF', or 'LOG'. Default: 'FULL'. .PARAMETER BackupPath Optional backup path. When specified, overrides the server default and is passed as -BackupPath to Invoke-sqmUserDatabaseBackup in Step 2. .PARAMETER ScheduleTime Start time of the schedule in format 'HH:mm'. When not specified, defaults depend on BackupType: '20:15' for FULL, '00:00' for LOG, '20:00' for DIFF (see description). .PARAMETER ScheduleDays Days of the week for the schedule. Valid values: 'Monday'..'Sunday', 'Weekdays', 'Weekend', 'EveryDay'. When not specified, defaults depend on BackupType (see description). .PARAMETER ScheduleIntervalMinutes Repeat interval within a day in minutes (e.g. 15 = every 15 minutes). 0 = run once at ScheduleTime. When not specified, defaults to 15 for -BackupType LOG and 0 (once) for FULL/DIFF (see description). .PARAMETER JobCategory SQL Agent job category. Default: 'Database Maintenance'. .PARAMETER UseExcludeTable When set, passes -UseExcludeTable to Invoke-sqmUserDatabaseBackup in Step 2. .PARAMETER CheckPreferredReplica When set, passes -CheckPreferredReplica to Invoke-sqmUserDatabaseBackup in Step 2. .PARAMETER IncludeSystemDatabases When set, passes -IncludeSystemDatabases to Sync-sqmBackupExcludeTable in Step 1. Note: system databases are not backed up by Invoke-sqmUserDatabaseBackup (Step 2). .PARAMETER MailTo Recipient email address. Passed as -MailTo to Invoke-sqmUserDatabaseBackup in Step 2. .PARAMETER MailProfile SQL Server Database Mail profile name. Passed as -MailProfile to Invoke-sqmUserDatabaseBackup. Default: 'Default'. .PARAMETER MailOnSuccess When set, passes -MailOnSuccess to Invoke-sqmUserDatabaseBackup in Step 2 so that a report mail is also sent on full success. .PARAMETER CleanupTime Retention period for old backup files of this BackupType in BackupPath, passed as -CleanupTime to Invoke-sqmUserDatabaseBackup in Step 2, e.g. '48h', '7d', '4w', '1m'. When not specified, defaults depend on BackupType (see description). Use -NoCleanup to disable cleanup entirely instead. .PARAMETER NoCleanup When set, no -CleanupTime is passed to Invoke-sqmUserDatabaseBackup in Step 2, so old backup files are never removed by this job. Ignored if -CleanupTime is also specified explicitly. .PARAMETER OperatorName SQL Agent operator name for failure email notification on the job level. .PARAMETER Update When set, replaces an existing job with the same name. .PARAMETER EnableException Throw exceptions immediately instead of returning error objects. .PARAMETER WhatIf Shows what would happen without making changes. .PARAMETER Confirm Request confirmation before creating the job. .EXAMPLE # Daily FULL backup, default schedule: every day at 20:15 New-sqmBackupMaintenanceJob -SqlInstance "SQL01" -BackupType FULL ` -UseExcludeTable -CheckPreferredReplica ` -MailTo "dba@company.com" -MailProfile "DBA-Mail" .EXAMPLE # Daily DIFF backup with exclude table New-sqmBackupMaintenanceJob -SqlInstance "SQL01" -BackupType DIFF ` -UseExcludeTable -ScheduleTime "22:00" .EXAMPLE # LOG backup, default schedule: every day, every 15 minutes starting 00:00, # default cleanup: .trn files older than 48h are removed after each run New-sqmBackupMaintenanceJob -SqlInstance "SQL01" -BackupType LOG -UseExcludeTable .EXAMPLE # LOG backup with custom retention and no automatic cleanup New-sqmBackupMaintenanceJob -SqlInstance "SQL01" -BackupType LOG -CleanupTime "24h" New-sqmBackupMaintenanceJob -SqlInstance "SQL01" -BackupType LOG -NoCleanup .EXAMPLE # Replace existing job New-sqmBackupMaintenanceJob -SqlInstance "SQL01" -BackupType FULL -Update .NOTES Prerequisites: dbatools, Invoke-sqmLogging Both job steps use the PowerShell subsystem and import sqmSQLTool at runtime. #> function New-sqmBackupMaintenanceJob { [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'None')] [OutputType([PSCustomObject])] param ( [Parameter(Mandatory = $false)] [string]$SqlInstance = $env:COMPUTERNAME, [Parameter(Mandatory = $false)] [System.Management.Automation.PSCredential]$SqlCredential, [Parameter(Mandatory = $false)] [string]$JobName, [Parameter(Mandatory = $false)] [ValidateSet('FULL', 'DIFF', 'LOG')] [string]$BackupType = 'FULL', [Parameter(Mandatory = $false)] [string]$BackupPath, [Parameter(Mandatory = $false)] [ValidatePattern('^\d{2}:\d{2}$')] [string]$ScheduleTime = '20:00', [Parameter(Mandatory = $false)] [ValidateSet('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday', 'Weekdays', 'Weekend', 'EveryDay')] [string[]]$ScheduleDays, [Parameter(Mandatory = $false)] [ValidateRange(0, 1440)] [int]$ScheduleIntervalMinutes = 0, [Parameter(Mandatory = $false)] [string]$JobCategory = 'Database Maintenance', [Parameter(Mandatory = $false)] [switch]$UseExcludeTable, [Parameter(Mandatory = $false)] [switch]$CheckPreferredReplica, [Parameter(Mandatory = $false)] [switch]$IncludeSystemDatabases, [Parameter(Mandatory = $false)] [string]$MailTo, [Parameter(Mandatory = $false)] [string]$MailProfile = 'Default', [Parameter(Mandatory = $false)] [switch]$MailOnSuccess, [Parameter(Mandatory = $false)] [ValidatePattern('^\d+[hdwm]$')] [string]$CleanupTime, [Parameter(Mandatory = $false)] [switch]$NoCleanup, [Parameter(Mandatory = $false)] [string]$OperatorName, [Parameter(Mandatory = $false)] [switch]$Update, [Parameter(Mandatory = $false)] [switch]$SkipAlwaysOnPropagation, [Parameter(Mandatory = $false)] [switch]$EnableException ) begin { $functionName = $MyInvocation.MyCommand.Name if (-not $script:dbatoolsAvailable) { $errMsg = "dbatools-Modul nicht gefunden." Invoke-sqmLogging -Message $errMsg -FunctionName $functionName -Level "ERROR" throw $errMsg } # Default-ScheduleDays je BackupType setzen wenn nicht explizit angegeben if (-not $PSBoundParameters.ContainsKey('ScheduleDays')) { switch ($BackupType) { 'FULL' { $ScheduleDays = @('EveryDay') } 'DIFF' { $ScheduleDays = @('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday') } 'LOG' { $ScheduleDays = @('EveryDay') } } } # Default-ScheduleTime je BackupType setzen wenn nicht explizit angegeben if (-not $PSBoundParameters.ContainsKey('ScheduleTime')) { switch ($BackupType) { 'FULL' { $ScheduleTime = '20:15' } 'DIFF' { $ScheduleTime = '20:00' } # LOG-Sicherungen sollen den ganzen Tag abdecken, nicht nur ab dem sonst # ueblichen Abend-Startpunkt - sonst wuerde "alle 15 Minuten" faktisch nur # ein paar Stunden am Abend bedeuten. 'LOG' { $ScheduleTime = '00:00' } } } # Default-ScheduleIntervalMinutes je BackupType setzen wenn nicht explizit angegeben if (-not $PSBoundParameters.ContainsKey('ScheduleIntervalMinutes')) { switch ($BackupType) { 'LOG' { $ScheduleIntervalMinutes = 15 } default { $ScheduleIntervalMinutes = 0 } } } # Default-CleanupTime je BackupType setzen wenn nicht explizit angegeben (ausser -NoCleanup) if (-not $NoCleanup -and -not $PSBoundParameters.ContainsKey('CleanupTime')) { switch ($BackupType) { 'FULL' { $CleanupTime = '4w' } 'DIFF' { $CleanupTime = '2w' } 'LOG' { $CleanupTime = '48h' } } } if ($NoCleanup) { $CleanupTime = $null } # JobName ohne explizite Angabe aus der Konfiguration lesen, abhaengig von -BackupType - # analog zu New-sqmOlaUsrDbBackupJob (OlaJobNameFull/Diff/Log). Vorher war der Default fest # auf 'sqm-BackupMaintenance-FULL' verdrahtet, unabhaengig vom gewaehlten BackupType - ein # Aufruf mit -BackupType DIFF ohne -JobName legte also einen Job an, der "...FULL" hiess, # obwohl er tatsaechlich ein DIFF-Job war. if (-not $PSBoundParameters.ContainsKey('JobName') -or [string]::IsNullOrWhiteSpace($JobName)) { $cfg = Get-sqmConfig $cfgKey = switch ($BackupType) { 'FULL' { 'BackupMaintenanceJobNameFull' } 'DIFF' { 'BackupMaintenanceJobNameDiff' } 'LOG' { 'BackupMaintenanceJobNameLog' } } $JobName = if ($cfg[$cfgKey]) { $cfg[$cfgKey] } else { "sqm-BackupMaintenance-$BackupType" } } $connParams = @{ SqlInstance = $SqlInstance } if ($SqlCredential) { $connParams['SqlCredential'] = $SqlCredential } } process { $result = [PSCustomObject]@{ SqlInstance = $SqlInstance JobName = $JobName BackupType = $BackupType Step1Command = $null Step2Command = $null ScheduleName = $null ScheduleDays = ($ScheduleDays -join ', ') ScheduleTime = $ScheduleTime CleanupTime = $CleanupTime Status = 'Unknown' Message = $null } try { Invoke-sqmLogging -Message "Starte Erstellung des Backup-Maintenance-Jobs '$JobName' auf $SqlInstance" -FunctionName $functionName -Level "INFO" # 1. Verbindung herstellen $sqlSrv = Connect-DbaInstance @connParams -ErrorAction Stop # 1a. Bei -UseExcludeTable: Tabelle synchronisieren und DDL-Trigger sicherstellen if ($UseExcludeTable) { Invoke-sqmLogging -Message "UseExcludeTable: Stelle sicher dass sqm_BackupExclude und DDL-Trigger vorhanden sind." -FunctionName $functionName -Level "INFO" $syncParams = @{ SqlInstance = $SqlInstance; SkipAlwaysOnPropagation = $true } if ($SqlCredential) { $syncParams['SqlCredential'] = $SqlCredential } Sync-sqmBackupExcludeTable @syncParams -ErrorAction SilentlyContinue | Out-Null $triggerParams = @{ SqlInstance = $SqlInstance; SkipAlwaysOnPropagation = $true } if ($SqlCredential) { $triggerParams['SqlCredential'] = $SqlCredential } Register-sqmBackupExcludeTrigger @triggerParams -ErrorAction SilentlyContinue | Out-Null } # 2. Job-Kategorie sicherstellen $existingCat = Get-DbaAgentJobCategory @connParams -Category $JobCategory -ErrorAction SilentlyContinue if (-not $existingCat) { New-DbaAgentJobCategory @connParams -Category $JobCategory -ErrorAction SilentlyContinue | Out-Null Invoke-sqmLogging -Message "Job-Kategorie '$JobCategory' wurde erstellt." -FunctionName $functionName -Level "INFO" } # 3. Bestehenden Job behandeln $existingJob = Get-DbaAgentJob @connParams -Job $JobName -ErrorAction SilentlyContinue if ($existingJob) { if (-not $Update) { $msg = "Job '$JobName' existiert bereits. Verwenden Sie -Update zum Ueberschreiben." Invoke-sqmLogging -Message $msg -FunctionName $functionName -Level "WARNING" $result.Status = 'AlreadyExists' $result.Message = $msg return $result } else { Remove-DbaAgentJob @connParams -Job $JobName -Confirm:$false -ErrorAction SilentlyContinue | Out-Null Invoke-sqmLogging -Message "Vorhandener Job '$JobName' wurde entfernt (Update)." -FunctionName $functionName -Level "INFO" } } # 4. Step 1 Command aufbauen: Sync-sqmBackupExcludeTable $step1Lines = [System.Collections.Generic.List[string]]::new() $step1Lines.Add("Import-Module sqmSQLTool -Force") $step1Lines.Add("`$params = @{ SqlInstance = '$SqlInstance' }") if ($IncludeSystemDatabases) { $step1Lines.Add("`$params['IncludeSystemDatabases'] = `$true") } $step1Lines.Add("Sync-sqmBackupExcludeTable @params") $step1Command = $step1Lines -join "`r`n" $result.Step1Command = $step1Command Invoke-sqmLogging -Message "Step 1 Command aufgebaut (Sync-sqmBackupExcludeTable)." -FunctionName $functionName -Level "INFO" # 5. Step 2 Command aufbauen: Invoke-sqmUserDatabaseBackup $step2Lines = [System.Collections.Generic.List[string]]::new() $step2Lines.Add("Import-Module sqmSQLTool -Force") $step2Lines.Add("`$params = @{ SqlInstance = '$SqlInstance'; All = `$true; BackupType = '$BackupType' }") if ($UseExcludeTable) { $step2Lines.Add("`$params['UseExcludeTable'] = `$true") } if ($CheckPreferredReplica) { $step2Lines.Add("`$params['CheckPreferredReplica'] = `$true") } if ($BackupPath) { $step2Lines.Add("`$params['BackupPath'] = '$BackupPath'") } if ($MailTo) { $step2Lines.Add("`$params['MailTo'] = '$MailTo'") } $step2Lines.Add("`$params['MailProfile'] = '$MailProfile'") if ($MailOnSuccess) { $step2Lines.Add("`$params['MailOnSuccess'] = `$true") } if ($CleanupTime) { $step2Lines.Add("`$params['CleanupTime'] = '$CleanupTime'") } $step2Lines.Add("Invoke-sqmUserDatabaseBackup @params") $step2Command = $step2Lines -join "`r`n" $result.Step2Command = $step2Command Invoke-sqmLogging -Message "Step 2 Command aufgebaut (Invoke-sqmUserDatabaseBackup)." -FunctionName $functionName -Level "INFO" # 6. WhatIf-Pruefung if (-not $PSCmdlet.ShouldProcess($SqlInstance, "Erstelle Job '$JobName' [$BackupType]")) { $result.Status = 'WhatIf' $result.Message = "WhatIf: Job '$JobName' wuerde erstellt werden." return $result } # 7. Job anlegen New-DbaAgentJob @connParams ` -Job $JobName ` -Category $JobCategory ` -Description "sqm BackupMaintenance $BackupType — Sync-sqmBackupExcludeTable + Invoke-sqmUserDatabaseBackup — $($ScheduleDays -join '/') $ScheduleTime" ` -EnableException -ErrorAction Stop | Out-Null Invoke-sqmLogging -Message "Job '$JobName' angelegt." -FunctionName $functionName -Level "INFO" # 8. Step 1 anlegen: Sync-BackupExcludeTable New-DbaAgentJobStep @connParams ` -Job $JobName ` -StepId 1 ` -StepName 'Sync-BackupExcludeTable' ` -Subsystem PowerShell ` -Command $step1Command ` -OnSuccessAction GoToNextStep ` -OnFailAction QuitWithFailure ` -EnableException -ErrorAction Stop | Out-Null Invoke-sqmLogging -Message "Step 1 'Sync-BackupExcludeTable' angelegt." -FunctionName $functionName -Level "INFO" # 9. Step 2 anlegen: Backup-UserDatabases-<BackupType> New-DbaAgentJobStep @connParams ` -Job $JobName ` -StepId 2 ` -StepName "Backup-UserDatabases-$BackupType" ` -Subsystem PowerShell ` -Command $step2Command ` -OnSuccessAction QuitWithSuccess ` -OnFailAction QuitWithFailure ` -EnableException -ErrorAction Stop | Out-Null Invoke-sqmLogging -Message "Step 2 'Backup-UserDatabases-$BackupType' angelegt." -FunctionName $functionName -Level "INFO" # 10. Hilfsfunktion: Wochentage aufloesen function ConvertTo-WeekdayInterval { param ([string[]]$Days) $expanded = foreach ($d in $Days) { switch ($d) { 'Weekdays' { 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' } 'Weekend' { 'Saturday', 'Sunday' } 'EveryDay' { 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday' } default { $d } } } return ($expanded | Select-Object -Unique) } # 11. Schedule anlegen $timeNormal = $ScheduleTime -replace ':', '' $intervalSuffix = if ($ScheduleIntervalMinutes -gt 0) { "_every$($ScheduleIntervalMinutes)min" } else { '' } $scheduleName = "sqm_BackupMaintenance_${BackupType}_${timeNormal}${intervalSuffix}" $result.ScheduleName = $scheduleName $expandedDays = ConvertTo-WeekdayInterval -Days $ScheduleDays $timeParts = $ScheduleTime -split ':' $startTime = '{0:D2}{1:D2}00' -f [int]$timeParts[0], [int]$timeParts[1] $schedParams = @{ SqlInstance = $SqlInstance Job = $JobName Schedule = $scheduleName Force = $true FrequencyType = 'Weekly' FrequencyInterval = $expandedDays StartTime = $startTime } if ($SqlCredential) { $schedParams['SqlCredential'] = $SqlCredential } if ($ScheduleIntervalMinutes -gt 0) { $schedParams['FrequencySubDayType'] = 'Minutes' $schedParams['FrequencySubDayInterval'] = $ScheduleIntervalMinutes $schedParams['EndTime'] = '235959' Invoke-sqmLogging -Message "Schedule '$scheduleName': woechentlich $($expandedDays -join '/'), Start $ScheduleTime, alle $ScheduleIntervalMinutes Minuten bis 23:59." -FunctionName $functionName -Level "INFO" } else { Invoke-sqmLogging -Message "Schedule '$scheduleName': woechentlich $($expandedDays -join '/') um $ScheduleTime." -FunctionName $functionName -Level "INFO" } New-DbaAgentSchedule @schedParams | Out-Null # 12. Operator fuer Fehler-Benachrichtigung if ($OperatorName) { $op = Get-DbaAgentOperator @connParams -Operator $OperatorName -ErrorAction SilentlyContinue if ($op) { Set-DbaAgentJob @connParams -Job $JobName -OperatorToEmail $OperatorName -EmailLevel OnFailure -ErrorAction SilentlyContinue | Out-Null Invoke-sqmLogging -Message "Operator '$OperatorName' fuer Fehler-Benachrichtigung gesetzt." -FunctionName $functionName -Level "INFO" } else { Invoke-sqmLogging -Message "Operator '$OperatorName' nicht gefunden — Benachrichtigung nicht konfiguriert." -FunctionName $functionName -Level "WARNING" } } $intervalInfo = if ($ScheduleIntervalMinutes -gt 0) { ", alle $ScheduleIntervalMinutes Min." } else { '' } $cleanupInfo = if ($CleanupTime) { ", Cleanup: $CleanupTime" } else { '' } $result.Status = 'Created' $result.Message = "Job '$JobName' ($BackupType) erstellt. Schedule: $($expandedDays -join '/') $ScheduleTime$intervalInfo$cleanupInfo" Invoke-sqmLogging -Message $result.Message -FunctionName $functionName -Level "INFO" } catch { $errMsg = $_.Exception.Message Invoke-sqmLogging -Message "Fehler bei Erstellung von Job '$JobName': $errMsg" -FunctionName $functionName -Level "ERROR" $result.Status = 'Failed' $result.Message = $errMsg if ($EnableException) { throw } } # AlwaysOn-Propagierung: Job auch auf Secondary-Repliken anlegen if (-not $SkipAlwaysOnPropagation -and $result.Status -eq 'Created') { try { $replicaQuery = "SELECT r.replica_server_name FROM sys.availability_replicas r WHERE r.replica_server_name <> @@SERVERNAME" $secondaries = Invoke-DbaQuery @connParams -Database master -Query $replicaQuery -ErrorAction SilentlyContinue foreach ($sec in $secondaries) { $secName = $sec.replica_server_name Invoke-sqmLogging -Message "AlwaysOn: Propagiere Job '$JobName' auf Secondary '$secName'." -FunctionName $functionName -Level "INFO" try { $secParams = @{ SqlInstance = $secName JobName = $JobName BackupType = $BackupType ScheduleTime = $ScheduleTime ScheduleDays = $ScheduleDays ScheduleIntervalMinutes = $ScheduleIntervalMinutes JobCategory = $JobCategory SkipAlwaysOnPropagation = $true Update = $true } if ($SqlCredential) { $secParams['SqlCredential'] = $SqlCredential } if ($BackupPath) { $secParams['BackupPath'] = $BackupPath } if ($UseExcludeTable) { $secParams['UseExcludeTable'] = $true } if ($CheckPreferredReplica) { $secParams['CheckPreferredReplica'] = $true } if ($IncludeSystemDatabases) { $secParams['IncludeSystemDatabases'] = $true } if ($MailTo) { $secParams['MailTo'] = $MailTo } if ($MailOnSuccess) { $secParams['MailOnSuccess'] = $true } if ($OperatorName) { $secParams['OperatorName'] = $OperatorName } if ($CleanupTime) { $secParams['CleanupTime'] = $CleanupTime } else { $secParams['NoCleanup'] = $true } $secParams['MailProfile'] = $MailProfile $secResult = New-sqmBackupMaintenanceJob @secParams Invoke-sqmLogging -Message "AlwaysOn '$secName': $($secResult.Status) — $($secResult.Message)" -FunctionName $functionName -Level "INFO" } catch { Invoke-sqmLogging -Message "AlwaysOn: Fehler bei Propagierung auf '$secName': $($_.Exception.Message)" -FunctionName $functionName -Level "WARNING" } } } catch { Invoke-sqmLogging -Message "AlwaysOn-Erkennung nicht verfuegbar oder kein AG konfiguriert." -FunctionName $functionName -Level "VERBOSE" } } return $result } } |