Like many DBAs, I suspect, I just haven’t found much need for PowerShell so far. There were lots of articles about how you could backup a database, run a query, get a list of databases etc. I just wasn’t seeing anything I couldn’t already do just fine in T-SQL. But with many Microsoft server products, PowerShell is taking over as the main management tool with the GUI serving as just a thin skin over the PowerShell API. Could this be coming our way?
So I’ve been meaning to give it a try and I just had a request to set up a daily FTP upload of some data to a partner organisation. I don’t know the command line stuff for FTP so this looked like a good excuse to learn a bit of PowerShell. After a while I had a script that worked nicely until I put it into a PowerShell step in a SQL Agent job. Then it failed complaining about how I was specifying non-existent arguments for a cmdlet. WTF?
After a lot of digging, I discovered that SQL Agent shells your script out to PowerShell 2.0. This is SQL Server 2014 and PowerShell 2.0 was RTMd in 2009. We’re up to PowerShell 5.0 now.
I imagine there are sound technical reasons for this but it’s a bit disappointing after all the hype.
David Wimbush 