Configuration

Invoke-sqmTsmConfiguration

Configures the IBM Spectrum Protect (TSM) client options file dsm.opt for SQL Server backup directories. Writes a managed EXCLUDE/INCLUDE block with per-path management classes, backing up the original file first. Supports local, remote, and INCLEXCL file targets.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: Yes (None)
Output: PSCustomObject

Execution Flow

START dbatools available? No throw error Yes Resolve BackupDirectory (registry → SMO → default) _FindDsmOptPath (env vars → registry → candidates) Target file? InclExclPath / UseInclExclFile / dsm.opt -InclExclPath Use explicit ie file (create if missing) -UseInclExclFile _ResolveInclExclPath from dsm.opt INCLEXCL option dsm.opt Read target file · create dsm.opt.bak backup Status=BackupFailed Build dtcSqlTools managed block EXCLUDE *.ldf / *.mdf / *.ndf INCLUDE User-db\* + Sys-db\* [MC] Custom -ExcludePatterns / -IncludeRule / -AdditionalIncludePaths override defaults Replace existing block or append to file ShouldProcess? WhatIf Status=WhatIf Yes Out-File target · write TXT report · Copy-sqmToCentralPath DONE
The managed block is delimited by * --- dtcSqlTools BEGIN --- and * --- dtcSqlTools END ---. Entries outside this block are preserved. -UseDiff forces management class to MC_B_NL.NL_42.42.NA. The tsm.opt path is auto-discovered via env vars → registry (IBM/Tivoli) → standard candidate paths.

Parameters

ParameterTypeRequiredDescription
-ComputerNameStringOptionalTarget server. Default: local computer.
-SqlInstanceStringOptionalSQL Server instance used to read the backup directory. Default: ComputerName.
-DsmOptPathStringOptionalExplicit path to dsm.opt. Auto-discovered when omitted.
-BackupDirectoryStringOptionalBase backup directory. Defaults to the value from the SQL Server instance.
-AdditionalIncludePathsString[]OptionalExtra directories added as INCLUDE entries (with default management class).
-ManagementClassStringOptionalDefault TSM management class (pattern MC_*). Default: MC_B_NL.NL_42.42.NA.
-ExcludePatternsString[]OptionalCustom EXCLUDE patterns. Defaults to *.ldf, *.mdf, *.ndf.
-IncludeRuleHashtable[]OptionalPer-path management class: @{ Path='...'; ManagementClass='...' }. Overrides the User-db/Sys-db defaults.
-UseInclExclFileSwitchOptionalResolve the INCLEXCL option from dsm.opt and write the managed block into that referenced file.
-InclExclPathStringOptionalExplicit path to the include/exclude file (overrides -UseInclExclFile). Created if missing.
-UseDiffSwitchOptionalForce management class to MC_B_NL.NL_42.42.NA (required for diff backup strategy).
-SqlCredentialPSCredentialOptionalSQL credentials for reading the backup directory.
-CredentialPSCredentialOptionalCredentials for remote file access.

Return Value

Returns PSCustomObject: ComputerName, DsmOptPath, TargetFile, BackupDirectory, ManagementClass, UseDiff, ExcludesWritten, IncludesWritten, BackupCreated, Status, Message, ReportPath.

Examples

Example 1 — Local, auto-discover everything

Invoke-sqmTsmConfiguration -ManagementClass MC_B_NL.NL_42.42.NA

Example 2 — Remote server with diff mode

Invoke-sqmTsmConfiguration -ComputerName "SQL01" -UseDiff

Example 3 — Write into INCLEXCL file with custom rules

Invoke-sqmTsmConfiguration -ComputerName "SQL01" -UseInclExclFile `
    -ExcludePatterns 'S:\...\*', '*:\...\*.ldf', '*:\...\*.mdf', '*:\...\*.ndf' `
    -IncludeRule @(
        @{ Path = 'F:\SQL\Backup\...\*';   ManagementClass = 'MC_B_NL.NL_35.35.NA' },
        @{ Path = 'F:\SQL\Backup\01Year\*'; ManagementClass = 'MC_B_NL_NL_365.365.NA' }
    )

Example 4 — WhatIf preview

Invoke-sqmTsmConfiguration -ComputerName "SQL01" -WhatIf