Explicit sql transaction in powershell

There are some hoops you need to jump through to get a multi-statement transaction committed via powershell. Default time-out is set to only one minute, so using the code below you can increase it.

$timeout = New-Object System.TimeSpan -ArgumentList @(5,0,0) # five hours
$options = [System.Transactions.TransactionScopeOption]::Required
$scope = New-Object -TypeName System.Transactions.TransactionScope -ArgumentList @($options,$timeout)

try
{
 #Multiple queries, loops or whatever fun here, inside transaction
 Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName1;
 Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName2;
 Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName3;
 
 $scope.Complete() #we tell transactionscope to commit when using ends (dispose)
 $scope.Dispose() #this is where the actual commit is sent to sql server
}
catch { 
 $scope.Dispose() #.Dispose() without .Complete() will roll back transaction
 throw ("Error while executing inside transaction, it has been rolled back: {0}" -f $_.Exception)
}