sqmSQLTool › Commands › Invoke-sqmDeployScripts

Invoke-sqmDeployScripts Configuration

Executes numbered SQL scripts (001_…sql, 002_…sql, …) from a directory in ascending numeric order against a target database. Pre-flight checks detect USE DATABASE mismatches and nested BEGIN TRANSACTION statements. All scripts run inside one outer BEGIN/COMMIT/ROLLBACK transaction by default. Creates a full database backup before execution and writes a detailed .log and .csv to -LogPath.

Execution Flow

START Validate ScriptPath exists | Ensure LogPath (create if missing) Discover *.sql → filter ^(\d+) prefix → sort ascending by numeric value Files without numeric prefix → Status=Skipped Pre-checks (all scripts scanned) Check 1: USE DATABASE → detect mismatch with $Database → warn + prompt user (not in -WhatIf) Check 2 (unless -NoWrapTransaction): BEGIN TRANSACTION inside script → nested tran warning + prompt -WhatIf mode? Print summary table; return (no SQL) -SkipBackup set? ShouldProcess confirm skip Backup-DbaDatabase → $defaultBackupPath\Sonderbackup\{database}_{timestamp}.bak | fail → abort If NOT -NoWrapTransaction: Invoke-DbaQuery 'BEGIN TRANSACTION' foreach $s in $sortedScripts if $anyFailed → Status=NotExecuted; continue Invoke-DbaQuery $scriptContent → Status=Success (duration logged) Error → $anyFailed=$true; Status=Failed | ROLLBACK TRANSACTION All succeed → COMMIT TRANSACTION Write .log → $LogPath\{timestamp}[_{JobNumber}]_Deploy.log Write .csv → same stem | Copy summary table → clipboard (clip.exe) Return List<PSCustomObject> per script + Summary { OverallStatus, Succeeded, Failed, BackupFile, LogFile, CsvFile } DONE

Synopsis

Script files must start with one or more digits (^\d+) to be included. Files without a numeric prefix are skipped with Status=Skipped. The numeric prefix is parsed as an integer for sorting, so 2_ sorts before 10_. Pre-checks scan every file for USE [database] pointing to a different database and for nested BEGIN TRANSACTION statements (only relevant in wrap-transaction mode). Both checks prompt the user interactively — in -WhatIf mode only the warning is logged. The backup is placed in a Sonderbackup subdirectory under the default SQL Server backup path. The summary object is the last element in the returned list and also copied to the clipboard via clip.exe.

Requires dbatools. The -JobNumber parameter embeds an order/ticket number into the log and CSV filename for traceability.
-SkipBackup requires ShouldProcess confirmation (ConfirmImpact=High). Declining the confirmation aborts the deployment. When the outer transaction is active, a script failure immediately issues ROLLBACK TRANSACTION, leaving the database unchanged.

Syntax

Invoke-sqmDeployScripts
    -SqlInstance <String>              # required
    -Database <String>                 # required
    -ScriptPath <String>               # required; directory with numbered .sql files
    -LogPath <String>                  # required; created if missing
    [-JobNumber <String>]              # embedded in log / csv filename
    [-QueryTimeout <Int>]              # default: 30 seconds per script
    [-SkipBackup]
    [-NoWrapTransaction]               # each script manages its own transactions
    [-SqlCredential <PSCredential>]
    [-WhatIf] [-Confirm]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString—Required. SQL Server instance (e.g. SQLSERVER01 or SQLSERVER01\INST1).
-DatabaseString—Required. Target database name.
-ScriptPathString—Required. Directory containing numbered .sql files.
-LogPathString—Required. Output directory for .log and .csv files. Created if it does not exist.
-JobNumberString—Optional order/ticket number embedded in the log filename.
-QueryTimeoutInt30Timeout in seconds per script execution.
-SkipBackupSwitch$falseSkip the pre-deployment backup. Requires interactive confirmation (ConfirmImpact=High).
-NoWrapTransactionSwitch$falseDisable the outer BEGIN/COMMIT/ROLLBACK wrapper. Each script handles its own transactions.
-SqlCredentialPSCredential—SQL Server authentication credentials.

Return Value

Returns an array where each element except the last is a per-script result, and the last element is the summary object.

Property (per-script)Description
ScriptNameFile name of the SQL script.
NumericPrefixParsed numeric order value.
StatusSuccess, Failed, NotExecuted (previous script failed), or Skipped (no numeric prefix).
DurationSecondsExecution time in seconds.
ErrorMessageException message on failure.
Property (summary object)Description
OverallStatusSUCCESS or FAILED.
Succeeded / Failed / Skipped / NotExecutedCounts per category.
BackupFilePath to the pre-deployment backup.
LogFile / CsvFilePaths to the output files.

Examples

# Basic deploy with automatic backup
Invoke-sqmDeployScripts -SqlInstance "SQLSERVER01" -Database "SalesDB" `
    -ScriptPath "D:\Deploy\v2.3" -LogPath "D:\Logs\Deploy"

# WhatIf dry run — pre-checks and summary table only
Invoke-sqmDeployScripts -SqlInstance "SQLSERVER01" -Database "SalesDB" `
    -ScriptPath "D:\Deploy\v2.3" -LogPath "D:\Logs\Deploy" -WhatIf

# Embed job number in log filename
Invoke-sqmDeployScripts -SqlInstance "SQLSERVER01" -Database "SalesDB" `
    -ScriptPath "D:\Deploy\v2.3" -LogPath "D:\Logs" -JobNumber "AU-2026-042"

# Scripts manage their own transactions
Invoke-sqmDeployScripts -SqlInstance "SQLSERVER01" -Database "SalesDB" `
    -ScriptPath "D:\Deploy\v2.3" -LogPath "D:\Logs" -NoWrapTransaction

# Check results
$r = Invoke-sqmDeployScripts -SqlInstance "SQL01" -Database "AppDB" `
    -ScriptPath "D:\v3" -LogPath "D:\Logs"
$r[-1]   # summary object
$r | Where-Object Status -eq 'Failed'