· Uwe Janke
Commands / Invoke-sqmTsmConfiguration
Configuration

Invoke-sqmTsmConfiguration

Configures the IBM Spectrum Protect (TSM) client options file (dsm.opt or a differently named file) 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 (TSM service -optfile → env/registry → *.opt) Target file? InclExclPath / UseInclExclFile / dsm.opt -InclExclPath Use explicit ie file (create if missing) -UseInclExclFile _ResolveInclExclPath from dsm.opt INCLEXCL option options file Read target file · create .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 options file is auto-discovered via the TSM services (-optfile=) → env vars → registry (IBM/Tivoli) → standard paths → any *.opt with TCPServeraddress, so it does not have to be called dsm.opt.

Parameters

ParameterTypeRequiredDescription
-ComputerNameStringOptionalTarget server. Default: local computer.
-SqlInstanceStringOptionalSQL Server instance used to read the backup directory. Default: ComputerName.
-DsmOptPathStringOptionalExplicit path to the options file (dsm.opt or a differently named file). 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 the options file and write the managed block into that referenced file. A relative value (file name only) is resolved against the directory of the options 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.
-ContinueOnErrorswitchOptionalContinue on error (not applicable here as there is no loop).
-EnableExceptionswitchOptionalThrow exceptions immediately (instead of silent error objects).
-OutputPathstringOptionalOutput directory for the configuration report. Default: Get-sqmDefaultOutputPath.

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, Options file with a different name, INCLEXCL file next to it

Invoke-sqmTsmConfiguration -DsmOptPath 'C:\Program Files\Tivoli\TSM\baclient\node01.opt' -UseInclExclFile

Example 5, WhatIf preview

Invoke-sqmTsmConfiguration -ComputerName "SQL01" -WhatIf