PowerShell transaction support for SqlServer schema changes

Answer to the core question

PowerShell does not currently provide a configuration switch or provider update that automatically enlists Invoke‑Sqlcmd (or any SqlServer provider command) in a Start‑Transaction/Undo‑Transaction block. The SqlServer provider lacks the ITransactionSupport interface, so schema changes executed via Invoke‑Sqlcmd are committed immediately and cannot be rolled back by PowerShell’s transaction infrastructure.

Confirmed facts

• The Start-Transaction cmdlet only enlists commands from providers that implement ITransactionSupport; the SqlServer provider does not.When Invoke-Sqlcmd runs outside an explicit SqlTransaction, each statement is auto‑committed by the underlying ADO.NET connection.Undo-Transaction will roll back only those commands that successfully enlisted; it has no effect on SqlServer statements that were not enlisted.

Likely explanation (subject to change)

If the SqlServer team were to add ITransactionSupport to the provider, PowerShell could automatically enlist SqlServer commands. This would require defining clear permission boundaries (e.g., limiting enlistment to connections with sufficient privileges) and deciding how nested PowerShell transactions map to nested SqlTransaction objects. Until such a change exists, users must manage transactions manually.

Steps to achieve safe rollback for schema changes

1. Create a System.Data.SqlClient.SqlConnection (or Microsoft.Data.SqlClient.SqlConnection) to the target database.
2. Open the connection and call $connection.BeginTransaction() to obtain a SqlTransaction object.
3. For each SQL statement (including DDL), create a SqlCommand, assign the connection and transaction, and execute it via $command.ExecuteNonQuery().
4. Wrap the whole block in a try/catch. In the catch, call $transaction.Rollback(); in the success path, call $transaction.Commit().
5. Finally, close the connection in a finally block.

# Example using Microsoft.Data.SqlClient
$connStr = "Server=myServer;Database=myDb;User Id=myUser;Password=myPwd;"
$connection = New-Object Microsoft.Data.SqlClient.SqlConnection $connStr
try {
    $connection.Open()
    $transaction = $connection.BeginTransaction()
    
    $cmd = $connection.CreateCommand()
    $cmd.Connection = $connection
    $cmd.Transaction = $transaction
    
    # Example schema change
    $cmd.CommandText = "ALTER TABLE dbo.MyTable ADD NewColumn INT NULL"
    $cmd.ExecuteNonQuery()
    
    # Additional statements …
    
    $transaction.Commit()
    Write-Host "Schema changes committed."
} catch {
    if ($transaction) { $transaction.Rollback() }
    Write-Error "Transaction rolled back due to error: $_"
} finally {
    $connection.Close()
}

Missing diagnostic detail that would affect the recommendation

To verify whether your specific environment exhibits the lack of enlistment, please provide the output of:

$PSVersionTable.PSVersion
Get-Module -ListAvailable SqlServer | Select-Object -ExpandProperty Version

If you are using a newer preview of the SqlServer provider that claims ITransactionSupport support, the answer may differ and manual ADO.NET transactions might not be required.