Powershell Transactions

PowerShell Help
1

I have a Powershell script that gets a list of files from a SQL PROC.

For each file, it then moves a file, then updates a DB record, then inserts a DB record.

How can I encapsulate the whole process (IO File Operation, plus the two SQL operations inside a Powershell transaction?

So if the file move fails, the subsequent SQL UPDATE and INSERT don’t happen, and if either the UPDATE or INSERT fail, the preceding file move gets rolled back?

I’m familiar with pure SQL transactions but I’ve pretty new to Powershell.

I’m using the Move-Item cmdlet to do the file operation which I believe supports Powershell transactions, and I’m using System.Data.SQLClient.SQLCommand to access SQL, although I was and could go back to using Invoke-SQLCmd if needed.

2

There’s nothing native that’s capable of that.

3

And to expound a bit - transactions in the file system were never fully implemented; only the registry is fully supported. But SQL doesn’t support PowerShell transactions, so there’s no way to “combine” a SQL transaction (which exists on the SQL Server) with a PowerShell transaction (which is local to the shell instance).

4

When you say, transaction are not implemented for file operations, why does the MS documentation for Move-Item contain a UseTransaction switch?

5

Because all the -Item commands work in all PSProviders. Not every PSProvider implements every feature; run Get-PSProvider to see those which support transactions.

It’s just like some don’t support -Filter.

6

Ok, I understand, thank you. I didn’t realise, Move-Item could be used for non file based operations.

7

You can build logic to do what you want. This is pseudo-code as an example:

$files = Invoke-Sqlcmd -Query 'Select * From MyFiles'

foreach ($file in $files) {
    try {
        #Attempt 
        Move-Item -Path $file -Destination 'C:\Windows\Temp' -ErrorAction Stop

        try {
            #Write to DB
            Invoke-Sqlcmd ... -ErrorAction Stop
        }
        catch {
            #Write to db, move back
            Move-Item -Path 'C:\Windows\Temp\File1.txt' -ErrorAction Stop
        }
    }
    catch {
        #File move failed
    }
}
8

Thank you, that’s very useful.

For each of the -ErrorActions, is it possible to trap the specific error message into a PS variable so I can then write some kind of audit row to record when a critical stop for either the file move, or SQL op fails?

9

Yes, you can catch specific errors and perform different actions. There is a free ebook above called The Big Book of PowerShell Error Handling that will show you how to do most error handling in Powershell. Another good resource: PowerShell: Everything you wanted to know about exceptions

Specifically, you are looking for catching typed exceptions and there is a ton of resources on the interwebs. Using try\catch and nesting, you should be able to do any kind of logic you want.

10

So I need to do a -ErrorAction Stop after the Move-Item because I understand that Cmdlet only soft errors, and will continue on error otherwise.

Do I need to do anything special to my DB calls that I’m doing like this, or will any error hit the Catch of the Try/Catch?

  $SQLQuery = "INSERT INTO dbo.Log (Col1,Col2) SELECT '$Val1', '$Val2'"
   
            $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
            $SqlCmd.CommandText = $SQLFileAuditInsertQuery
            $SqlCmd.Connection = $SqlConnection
            $SqlCmd.CommandTimeout = $SQLTimeout
            $SqlCmd.ExecuteNonQuery()    
            $SqlConnection.Close()
11

Will this process run from a single place? Two options:

  1. Put SQLPS Powershell Module on the box. This will make a pre-requisite for the script, but rather than executing SQL with a lot of code, you can leverage the Invoke-SqlCmd cmdlet. https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-2017
  2. Place all of your SQL code into a function. You will need to catch errors in several places, like the connection and the Execute lines, so if you're going to call in multiple places, you definitely want to use a function. If you don't know or control where the code will execute to put a pre-requisite module, then leverage a function.

If you contain your SQL call in Invoke-SQLCmd or a custom function, then you can have a single try\catch around those calls and keep your code clean.