PowerShell: Running Multiple Invoke-Sqlcmd Within a Transaction

I had a requirement where I want to run set of .SQL scripts in a folder, either all scripts should get executed or none should get executed. So this is a quick post on how you can run multiple Invoke-Sqlcmd commands and that's within a transaction using PowerShell. I was initially hoping Invoke-Sqlcmd will accept an argument for Transaction, but apparently not.

This is accomplished via a native System.Transactions. This is inspired by this StackOverflow answer.

$filePath = "$(System.DefaultWorkingDirectory)\scripts"
$files = Get-ChildItem $filePath -Filter "*.sql"
 
$options = [System.Transactions.TransactionScopeOption]::RequiresNew
$timeout = New-Object System.TimeSpan -ArgumentList @(0, 10, 0) #10 minutes
$scope = New-Object System.Transactions.TransactionScope -ArgumentList ($options, $timeout)
 
Write-Host " -> Starting a transaction."
 
try {
    foreach ($f in $files) {
        Write-Host " -> Executing script "$filePath\$f""
 
        Invoke-Sqlcmd `
            -ServerInstance "$(MsSqlServer)" `
            -Database "$(Database)" `
            -Username "$(Login)" `
            -Password "$(Password)" `
            -Inputfile "$filePath\$f" `
            -ConnectionTimeout 120 `
            -ErrorAction 'Stop'
    } 
    
    $scope.Complete()
    $scope.Dispose()
 
    Write-Host " -> Completed the transaction."
}
catch {
    Write-Host "Error Message: " $_.Exception.Message

    $scope.Dispose()
}

So here either all the changes in SQL scripts will get applied or if any errors, none will get applied.

Happy Coding.

Regards,
Jaliya