Invoke-Ola* cmdlets

[niphlod]

Feature Request

Being able to invoke ola's procedures with all the param validation would be a good thing. I'll take this upon myself.

• Invoke-OlaDbBackup
• Invoke-OlaIndexOptimize
• Invoke-OlaDbIntegrity

[potatoqualitee]

awesome_sauce x2

[wsmelton]

I'll be interested to see how you handle the output of the command when you pass in @Execute=N'N'. This causes a TON of text to spit out on large databases. Be nice to parse that out into some readable format, not sure if that is too far fetched.

[wsmelton]

Invoke-OlaDbIntegrity

We already have Start-DbccCheck as an internal function...why not just make that external and then add in logic from Ola's processing. I don't recall his proc doing anything special.

[andreasjordan]

Hi @niphlod - still working on this?

[wsmelton]

Closing, if we want to revisit can open a new issue.

[potatoqualitee]

@claude ultrathink a complete plan for @niphlod's proposed commands based on Ola Hallengren's current scripts (attached)

MaintenanceSolution.sql

The scripts should include ValidateSets and no ParameterSets. Other validation as required.

After reviewing the uploaded Ola script, any other command suggestions? I dont want 15 but popular or combo kills are good.

[claude[bot]]

Claude finished @potatoqualitee's task —— View job

---

Planning Invoke-Ola* Commands ✅

Summary

I've completed a comprehensive analysis of Ola Hallengren's MaintenanceSolution.sql and created detailed specifications for three PowerShell wrapper commands. The plan includes full parameter mappings, validation attributes, and implementation guidance following dbatools conventions.

---

Command Plan for Ola Hallengren Maintenance Solution Wrappers

Based on analysis of Ola's MaintenanceSolution.sql (version 2022-12-03), here's the comprehensive plan for three PowerShell commands that wrap his stored procedures with proper parameter validation.

Design Principles

1. No ParameterSets - Use Test-Bound pattern for validation with clear, helpful error messages
2. ValidateSets - Used extensively for Ola's Y/N parameters and enumerated options
3. Parameter Validation - Include ValidateRange and other attributes where appropriate
4. PowerShell Naming - Convert SQL naming to PowerShell conventions while maintaining clarity
5. Leverage Existing Infrastructure - Build on Install-DbaMaintenanceSolution pattern
6. Smart Defaults - Sensible defaults that match Ola's stored procedure defaults

---

1. Invoke-DbaOlaDbBackup

Wraps DatabaseBackup stored procedure for comprehensive backup operations.

Parameters

Core Connection & Database Selection:

[Parameter(Mandatory, ValueFromPipeline)]
[DbaInstanceParameter[]]$SqlInstance

[PSCredential]$SqlCredential

[string]$Database = "master"  # Where Ola's procedures are installed

[string[]]$Databases = $null  # Databases to backup (supports SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES, etc.)

Backup Type & Location:

[Parameter(Mandatory)]
[ValidateSet("FULL", "DIFF", "LOG")]
[string]$BackupType

[string]$Directory = $null  # Backup directory path

[string]$URL = $null  # Azure blob storage URL

Backup Options (Y/N switches converted to PowerShell):

[ValidateSet("Y", "N")]
[string]$Verify = "N"

[ValidateSet("Y", "N")]
[string]$Compress = $null  # NULL uses SQL instance default

[ValidateSet("Y", "N")]
[string]$CopyOnly = "N"

[ValidateSet("Y", "N")]
[string]$ChangeBackupType = "N"  # Convert DIFF->FULL or LOG->FULL/DIFF if needed

[ValidateSet("Y", "N")]
[string]$CheckSum = "N"

[ValidateSet("Y", "N")]
[string]$Init = "N"

[ValidateSet("Y", "N")]
[string]$Format = "N"

Cleanup & Retention:

[int]$CleanupTime = $null  # Hours to retain backups

[ValidateSet("AFTER_BACKUP", "BEFORE_BACKUP")]
[string]$CleanupMode = "AFTER_BACKUP"

Performance & Parallelization:

[int]$BlockSize = $null

[int]$BufferCount = $null

[int]$MaxTransferSize = $null

[int]$NumberOfFiles = $null  # Stripe backups across multiple files

[int]$MinBackupSizeForMultipleFiles = $null

[int]$MaxFileSize = $null

[int]$Threads = $null  # For backup software compression

[int]$Throttle = $null

[ValidateSet("Y", "N")]
[string]$DatabasesInParallel = "N"

Encryption:

[ValidateSet("Y", "N")]
[string]$Encrypt = "N"

[ValidateSet("AES_128", "AES_192", "AES_256", "TRIPLE_DES_3KEY")]
[string]$EncryptionAlgorithm = $null

[string]$ServerCertificate = $null

[string]$ServerAsymmetricKey = $null

[string]$EncryptionKey = $null

[string]$Credential = $null  # SQL credential for URL backups

Advanced Options:

[string]$BackupSoftware = $null  # Third-party backup software

[int]$CompressionLevel = $null  # For third-party compression

[string]$Description = $null

[ValidateSet("Y", "N")]
[string]$ReadWriteFileGroups = "N"

[ValidateSet("Y", "N")]
[string]$OverrideBackupPreference = "N"

[ValidateSet("Y", "N")]
[string]$NoRecovery = "N"  # For log shipping scenarios

[ValidateSet("Y", "N")]
[string]$AdaptiveCompression = $null

[ValidateRange(0, 100)]
[int]$ModificationLevel = $null  # % modified for ChangeBackupType

[int]$LogSizeSinceLastLogBackup = $null  # MB

[int]$TimeSinceLastLogBackup = $null  # Minutes

Mirror Backup:

[string]$MirrorDirectory = $null

[int]$MirrorCleanupTime = $null

[ValidateSet("AFTER_BACKUP", "BEFORE_BACKUP")]
[string]$MirrorCleanupMode = "AFTER_BACKUP"

[string]$MirrorURL = $null

Availability Groups:

[string[]]$AvailabilityGroups = $null

[ValidateSet("ALL", "READ_ONLY", "READ_WRITE")]
[string]$Updateability = "ALL"

File Structure Customization:

[string]$DirectoryStructure = "{ServerName}`${InstanceName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}"

[string]$AvailabilityGroupDirectoryStructure = "{ClusterName}`${AvailabilityGroupName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}"

[string]$FileName = "{ServerName}`${InstanceName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}"

[string]$AvailabilityGroupFileName = "{ClusterName}`${AvailabilityGroupName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}"

[string]$FileExtensionFull = $null

[string]$FileExtensionDiff = $null

[string]$FileExtensionLog = $null

Third-Party Integration:

[string]$DataDomainBoostHost = $null

[string]$DataDomainBoostUser = $null

[string]$DataDomainBoostDevicePath = $null

[string]$DataDomainBoostLockboxPath = $null

[ValidateSet("Y", "N")]
[string]$ObjectLevelRecoveryMap = "N"

Execution Control:

[ValidateSet("Y", "N")]
[string]$ExcludeLogShippedFromLogBackup = "Y"

[ValidateSet("Y", "N")]
[string]$DirectoryCheck = "Y"

[string]$StringDelimiter = ","

[ValidateSet("DATABASE_NAME_ASC", "DATABASE_NAME_DESC", "DATABASE_SIZE_ASC", "DATABASE_SIZE_DESC", "LOG_SIZE_SINCE_LAST_LOG_BACKUP_ASC", "LOG_SIZE_SINCE_LAST_LOG_BACKUP_DESC")]
[string]$DatabaseOrder = $null

[ValidateSet("Y", "N")]
[string]$LogToTable = "N"

[ValidateSet("Y", "N")]
[string]$Execute = "Y"  # Y=execute, N=generate scripts only

[switch]$EnableException

---

2. Invoke-DbaOlaIndexOptimize

Wraps IndexOptimize stored procedure for intelligent index maintenance.

Parameters

Core Connection & Database Selection:

[Parameter(Mandatory, ValueFromPipeline)]
[DbaInstanceParameter[]]$SqlInstance

[PSCredential]$SqlCredential

[string]$Database = "master"  # Where Ola's procedures are installed

[string[]]$Databases = $null  # Databases to optimize

Fragmentation Thresholds & Actions:

[string]$FragmentationLow = $null  # Comma-delimited: INDEX_REORGANIZE, INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE

[string]$FragmentationMedium = "INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE"

[string]$FragmentationHigh = "INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE"

[ValidateRange(0, 100)]
[int]$FragmentationLevel1 = 5  # Low threshold %

[ValidateRange(0, 100)]
[int]$FragmentationLevel2 = 30  # Medium threshold %

[int]$MinNumberOfPages = 1000  # Minimum pages to process index

[int]$MaxNumberOfPages = $null  # Maximum pages to process index

Index Rebuild/Reorganize Options:

[ValidateSet("Y", "N")]
[string]$SortInTempdb = "N"

[int]$MaxDOP = $null

[ValidateRange(0, 100)]
[int]$FillFactor = $null

[ValidateSet("Y", "N")]
[string]$PadIndex = $null

[ValidateSet("Y", "N")]
[string]$LOBCompaction = "Y"

[ValidateSet("Y", "N")]
[string]$Resumable = "N"  # SQL 2017+

[int]$WaitAtLowPriorityMaxDuration = $null  # Minutes, for online operations

[ValidateSet("NONE", "SELF", "BLOCKERS")]
[string]$WaitAtLowPriorityAbortAfterWait = $null

Statistics Options:

[ValidateSet("ALL", "INDEX", "COLUMNS")]
[string]$UpdateStatistics = $null

[ValidateSet("Y", "N")]
[string]$OnlyModifiedStatistics = "N"

[ValidateRange(0, 100)]
[int]$StatisticsModificationLevel = $null  # % rows modified

[ValidateRange(0, 100)]
[int]$StatisticsSample = $null  # Sample percentage

[ValidateSet("Y", "N")]
[string]$StatisticsResample = "N"

Scope & Selection:

[ValidateSet("Y", "N")]
[string]$PartitionLevel = "Y"

[ValidateSet("Y", "N")]
[string]$MSShippedObjects = "N"

[string[]]$Indexes = $null  # Specific indexes to process

Availability Groups:

[string[]]$AvailabilityGroups = $null

Execution Control:

[int]$TimeLimit = $null  # Seconds

[int]$Delay = $null  # Seconds between operations

[int]$LockTimeout = $null  # Seconds

[ValidateRange(1, 25)]
[int]$LockMessageSeverity = 16

[string]$StringDelimiter = ","

[ValidateSet("DATABASE_NAME_ASC", "DATABASE_NAME_DESC", "DATABASE_SIZE_ASC", "DATABASE_SIZE_DESC")]
[string]$DatabaseOrder = $null

[ValidateSet("Y", "N")]
[string]$DatabasesInParallel = "N"

[string]$ExecuteAsUser = $null

[ValidateSet("Y", "N")]
[string]$LogToTable = "N"

[ValidateSet("Y", "N")]
[string]$Execute = "Y"  # Y=execute, N=generate scripts only

[switch]$EnableException

---

3. Invoke-DbaOlaDbIntegrity

Wraps DatabaseIntegrityCheck stored procedure for DBCC operations.

Note: The existing internal Start-DbccCheck function is very basic (only CHECKDB/CHECKTABLE with MAXDOP). Ola's DatabaseIntegrityCheck is much more comprehensive with CHECKALLOC, CHECKCATALOG, CHECKFILEGROUP, and many options. This command would be a significant upgrade.

Parameters

Core Connection & Database Selection:

[Parameter(Mandatory, ValueFromPipeline)]
[DbaInstanceParameter[]]$SqlInstance

[PSCredential]$SqlCredential

[string]$Database = "master"  # Where Ola's procedures are installed

[string[]]$Databases = $null  # Databases to check

Check Commands:

[ValidateSet("CHECKDB", "CHECKALLOC", "CHECKCATALOG", "CHECKFILEGROUP", "CHECKTABLE")]
[string[]]$CheckCommands = "CHECKDB"  # Can specify multiple

Check Options:

[ValidateSet("Y", "N")]
[string]$PhysicalOnly = "N"

[ValidateSet("Y", "N")]
[string]$DataPurity = "N"

[ValidateSet("Y", "N")]
[string]$NoIndex = "N"

[ValidateSet("Y", "N")]
[string]$ExtendedLogicalChecks = "N"

[ValidateSet("Y", "N")]
[string]$TabLock = "N"

[int]$MaxDOP = $null

Scope Selection:

[string[]]$FileGroups = $null  # For CHECKFILEGROUP

[string[]]$Objects = $null  # For CHECKTABLE (schema.table format)

Availability Groups:

[string[]]$AvailabilityGroups = $null

[ValidateSet("ALL", "PRIMARY", "SECONDARY")]
[string]$AvailabilityGroupReplicas = "ALL"

[ValidateSet("ALL", "READ_ONLY", "READ_WRITE")]
[string]$Updateability = "ALL"

Execution Control:

[int]$TimeLimit = $null  # Seconds

[int]$LockTimeout = $null  # Seconds

[ValidateRange(1, 25)]
[int]$LockMessageSeverity = 16

[string]$StringDelimiter = ","

[ValidateSet("DATABASE_NAME_ASC", "DATABASE_NAME_DESC", "DATABASE_SIZE_ASC", "DATABASE_SIZE_DESC", "LAST_GOOD_CHECKDB_ASC", "LAST_GOOD_CHECKDB_DESC")]
[string]$DatabaseOrder = $null

[ValidateSet("Y", "N")]
[string]$DatabasesInParallel = "N"

[ValidateSet("Y", "N")]
[string]$LogToTable = "N"

[ValidateSet("Y", "N")]
[string]$Execute = "Y"  # Y=execute, N=generate scripts only

[switch]$EnableException

---

Additional Command Suggestions

Based on Ola's solution and common usage patterns, here are two additional commands that would provide significant value:

4. Invoke-DbaOlaCommand (Utility/Combo Command)

A flexible wrapper that can invoke any of the three main procedures with appropriate parameter passing. Useful for:

• Scripted maintenance workflows combining multiple operations
• Executing custom sequences (e.g., integrity check → index optimize → full backup)
• Advanced users who want one command with dynamic procedure selection

[Parameter(Mandatory)]
[ValidateSet("DatabaseBackup", "IndexOptimize", "DatabaseIntegrityCheck")]
[string]$Procedure

# Then dynamic parameters based on procedure selection
# OR a hashtable of parameters to pass through:
[hashtable]$Parameters

5. Get-DbaOlaCommandLog (Already exists as Get-DbaMaintenanceSolutionLog)

The existing Get-DbaMaintenanceSolutionLog command already handles reading from Ola's CommandLog table, so no additional command needed here.

---

Implementation Notes

Common Patterns

1. Database Parameter Handling

   • Support for special keywords: SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES, AVAILABILITY_GROUP_DATABASES
   • Support for exclusions with minus prefix: -tempdb
   • Comma-delimited list support
   • Database name validation

2. Y/N Parameter Conversion

   • Ola uses nvarchar with 'Y'/'N' values
   • PowerShell commands should use [ValidateSet("Y", "N")] with [string] type
   • Alternative: Convert to [switch] parameters, but this loses Ola's three-state logic (Y/N/NULL)
   • Recommendation: Keep as [ValidateSet("Y", "N")] to preserve NULL option for defaults

3. Procedure Verification

   • Check that procedures exist in target database before execution
   • Validate version compatibility (check for required parameters in procedure definition)
   • Provide clear error messages if procedures missing or outdated

4. Output Handling

   • When Execute = "Y": Return execution results with status
   • When Execute = "N": Return generated T-SQL commands for review
   • Parse CommandLog table if LogToTable = "Y" for detailed results
   • Handle verbose output properly (Ola's procedures use RAISERROR for progress)

5. Pipeline Support

   • Support piping from Get-DbaDatabase for database operations
   • Support piping from Connect-DbaInstance for instance-level operations

6. WhatIf/Confirm Support

   • All three commands should support [CmdletBinding(SupportsShouldProcess)]
   • Proper confirmation for destructive operations

Special Considerations

For Invoke-DbaOlaDbBackup:

• Validate that Directory or URL is specified (but not both)
• Validate that BackupType = "LOG" only runs against databases in FULL or BULK_LOGGED recovery model
• When ChangeBackupType = "Y", require ModificationLevel parameter
• Special handling for AG-aware backups with OverrideBackupPreference

For Invoke-DbaOlaIndexOptimize:

• The fragmentation action strings are comma-delimited with specific keywords
• Need to validate that FragmentationLevel1 < FragmentationLevel2
• Resumable = "Y" requires SQL Server 2017+ (version check)
• WaitAtLowPriority* parameters require SQL Server 2014+ and online operations

For Invoke-DbaOlaDbIntegrity:

• When CheckCommands includes CHECKFILEGROUP, require FileGroups parameter
• When CheckCommands includes CHECKTABLE, require Objects parameter
• ExtendedLogicalChecks requires SQL Server 2008 R2+
• Can be enhanced beyond internal Start-DbccCheck with all Ola's options

Testing Strategy

For each command, tests should verify:

1. Parameter validation (ValidateSets, ranges, required combinations)
2. Procedure existence checking
3. T-SQL generation (Execute = "N")
4. Actual execution (Execute = "Y") against test databases
5. Pipeline input support
6. WhatIf/Confirm behavior
7. Output parsing and object return
8. Error handling for invalid parameters/combinations

---

Summary

Recommended Commands:

1. ✅ Invoke-DbaOlaDbBackup - Full DatabaseBackup wrapper (66 parameters)
2. ✅ Invoke-DbaOlaIndexOptimize - Full IndexOptimize wrapper (35 parameters)
3. ✅ Invoke-DbaOlaDbIntegrity - Full DatabaseIntegrityCheck wrapper (16 parameters)
4. ⚠️ Invoke-DbaOlaCommand - Optional unified wrapper for advanced scenarios

NOT Recommended:

• Separate commands for CommandExecute (internal execution engine)
• Get-DbaOlaCommandLog (already exists as Get-DbaMaintenanceSolutionLog)

This plan provides comprehensive wrappers for Ola's three main maintenance procedures with proper PowerShell parameter validation, following dbatools conventions, and avoiding ParameterSets in favor of Test-Bound validation with helpful error messages.

---

[potatoqualitee]

@niphlod pls advise and if you like, guide claude on design. if you like it, then i think we should do one PR per command to get the highest quality code.

[niphlod]

this "can" be done as long as what we validate is the actual content of the sprocs .... we should insert at least a check on the version of the versions of the underlying stored procedures (because validation "could" be different if parameters change). Writing regression tests for everything is another huge deal, as it'll catch regressions early. we shouldn't do invoke-dbaolacommand.