18 September 2012

The PoSh DBA: Solutions using PowerShell and SQL Server

PowerShell is worth using when it is the quickest way to providing a solution. For the DBA, it is much more than getting information from SQL Server instances via PowerShell; it can also be run from SQL Server as part of a system that helps with administrative and monitoring tasks. Laerte explains how.

Why should a DBA learn PowerShell ? It is all about solutions. In this article, I want to explain how one can integrate PowerShell, TSQL, SQL Jobs and SQL WMI alerts into a complete solution. I will go further in this topic in my new written project along three great friends. Stay Tuned, as we will soon have a complete guide about day-to-day solutions for the DBA, using PowerShell and SQL Server.

When you read about using PowerShell and SQL Server, you are usually learning about the way that you use PowerShell to access SQL Server. Sometimes, instead, you’ll want to use PowerShell directly from SQL Server to create solutions. You may want to do it from TSQL, getting data back in a form that can then be inserted into a table, or execute it on the server from SSMS. You might want to run PowerShell scripts from the SQL Server Agent, or to set up sophisticated alerts using WMI that then execute jobs that are written in PowerShell. I’ll be showing you how to do all this; but let’s take things in easy stages.

Running PowerShell scripts from SSMS Query Editor

Did you know that you can run your PowerShell cmdlets and functions, along with their parameters, very simply from the Management Studio (SSMS) Query Editor, executing them on the server? Yeah, by using xp_cmdshell. Before I start showing you how to use xp_cmdshell to run PowerShell cmdlets from within TSQL, I must make you aware that, by enabling xp_cmdshell on a server, you’re creating potential security issues. There are good reasons why xp_CmdShell is disabled by default.

When using xp_CmdShell to run PowerShell in SSMS , you’ll Just need to remember three things:

  • Use ‘Results to text’ in SSMS, from the menu item Query -> ‘Results to…’ to see an easily-readable output from PowerShell.
  • You are not running PowerShell on your own machine, but on the server that is hosting the SQL Server instance you’re connected to. The userID which you are using to open a connection will need execute permissions. You’ll need to make sure that your PowerShell profile has all the functions installed that you need to run. What account is this? If you’re using a SysAdmin account, then the PowerShell command will be executed under the ‘SQL Server service account’, which is the security context in which the SQL Server service is running. Otherwise, you’ll need to set up a proxy account on that server using sp_xp_CmdShell_proxy_account
  • Make sure that PowerShell is installed to the correct version on the host server.

Once you’ve enabled xp_CmdShell, and you have the necessary permissions to use it, PowerShell can give you valuable information easily. I’ll show you a couple of examples: Getting Disk space, and seeing what services are running.

Seeing what services are running

We’ll start simply by listing all the services on the server.


You have the full list of services, whatever their status. What if you wanted only those that had stopped? You’ll need to combine two CmdLets to do this in a pipeline , so it is now time to use a command-line parameter to run the command, the -Command parameter


And with only a small change we can see all the SQL services that have stopped.


But you can also query a server remotely once it has been configured :

Getting the free disk space

Here, we are using a Get-Diskspace function (@sqlvariant) for the host of the SQL Server instance. This requires a function that you can download at the bottom of the article, and which will need to be placed on the server.


To get the disk space for a different, remote, server, for example, use …


To get the disk space for all the Servers into a file called Servers.txt:


Alternatively, to get just the percentage disk space, you can also use Get-Counter and \LogicalDisk(*)\% Free Space to get all counter instances. You can do this locally, for the host of your instance ….


…or for a remote server


So can you do more than this and run scripts the same way? Well, no, because there is a limitation. You can’t use the ” (double-quote) character, which is essential for PowerShell, because it is used in the command-line parameter to delimit the script-fragment being executed. To do this, you’ll need to save the script as a file and execute that.

Returning data from PowerShell

If you run a PowerShell CmdLet in xp_cmdshell, how do you get the data back into SQL as tabular data? We’ve shown you the output, but it is not immediately obvious as to how to read it. Xp_cmdshell actually returns a table consisting of a single column called ‘output’. You can insert it into a table using INSERT..EXEC, but INSERT..EXEC has certain restrictions. You cannot nest them and it cannot contain an output clause.

However we can use this method to return an XML representation of the PowerShell objects being returned. All we then have to do is to shred it into a relational form and create a table.

Taking a more refined version of the previous PowerShell command

This will give the result

Which any DBA will recognise as data! What have we done here? We have chosen to create an XML representation of the report which was then returned to SQL Server line by line. We had to re-assemble it into an XML file and shred it in the way that we needed. This is laborious for an ad-hoc request but it makes a lot of sense for a scheduled monitoring job.

Running Scripts That has Posh Jobs on a SQL Agent Job

PowerShell can be run from the scheduler to do regular jobs such as ETL. Although this generally takes little more effort than testing it in the PowerShell ISE, just sometimes PowerShell gives you a culture-shock. Sometimes things happen that you don’t expect, even though they make sense when you think about it later. For example, I recently developed a script that created a lot of PowerShell Jobs. For some reason, when I ran it in the PowerShell Command-line console, it all worked fine. When I then ran it on the SQL Server scheduler, using a CMDExec jobtype, calling PowerShell, nothing happened: and there was no error message in Jobs History.

The script invoked a process that retrieved all the windows updates applied to a list of servers in the past 24 hours, and saved the results into a SQL Server Table within a repository server. It was using runspaces, though what I’ll describe will be useful for anyone that is using background jobs. I was using PowerShell to create jobs that ran in parallel, one for each server I was getting information from. I was getting a list of servers from a file called ‘c:\temp\Servers.txt’ and for each server name, I was starting a background job on the local computer. This job then obtained the windows update information for the server which was then filtered by a Where cmdlet for only those within the past day. The results were reported back to a tbl_WindowsPatches table in a SQL Server repository. The code is this:

You can download the Get-WindowsUpdates , Out-Datatable and Write-DataTable functions at the top of this article in the Functions.psm1 file . In this case I have a module called Functions that join all these functions . As I am using PowerShell jobs and it runs in another runspace, these functions are not visible. So I need to explicit load them in , in the functions module, in the line
$_ -InitializationScript{Ipmo Functions -Force -DisableNameChecking}

Why should it have worked in the PowerShell console, but not when run from the SQL Server Agent? My first test was to create a .bat and run :

Ok. So what is happening ? Nothing was stored. My script was creating the jobs, each of which was running in its own independent runspace, and then it was closing the PowerShell session. The script seemed to run in an open command console, but not when it was closed immediately after the script was run. Were these separate jobs being closed prematurely when the parent session was closed?

To check that this was the problem, I added, to the line in the .bat file that executed PowerShell, the parameter noexit so as to prevent the closure of the session…

… and it worked. Why? My script was creating the jobs, each of which was running in its own independent runspace, and then closed the PowerShell session. The PowerShell jobs hadn’t been completed when the main session was closed, so nothing was returned to the table, but no error was raised. Why was the session closed before the jobs had completed? It was because the PowerShell jobs run in another runspace, but within the same session that was called. The session must not be closed until all PowerShell jobs finish.

What do I need to do? All I have to do is to wait until all PowerShell Jobs are finished, it is as simple as that. I’d forgotten to add a ‘wait-job *’!

The final code is :

I save this code into C:\Temp\Automation\GetWindowsUpdates.ps1 on the server,

The Command to run as a CMDExec SQL Agent Step is :

Outputting SQL Agent Job PowerShell in Job History

Although I’ve shown you how to get data from a PowerShell job that is running in a batch, there are times when all you need is a record of what a script did, so you can check afterwards

If, for example, you have a SQL Agent PowerShell Job that deletes old files in a log shipping process, and you want to output a list of the files that were removed in the Job History you can just to use write-output. This script shows you what happens:

Then if you look at your Job History, you’ll then see the list.


The trick to get this to work with SQL Agent PowerShell Job, is that instead of using

you need to use format specifier :

Triggering PowerShell jobs with SQL WMI Alerts

This solution is using PowerShell scripts of course, SQL Server Jobs and a SQL WMI Alert

Imagine that you have a download folder on a SQL Server host that has several files downloaded automatically by FTP. It is called FTPDownload. A file with a specific name is downloaded once a day. The contents of this file must be loaded into a staging table in another SQL Server.

Firstly, lets create the Windows Query Language (WQL) query to monitor the specific file in a specific folder : for us the folder is c:\FTPDownload and the file is FileImport.CSV

To a complete explanation about the WMI and WQL I suggest you read the excellent ebook from my good friend and PowerShell Jedi Ravikanth Chaganti –WMI Query Language via PowerShell

Then Lets create the Job called IMPORTCSV with a PowerShell code called importCSV.ps1 on c:\FTPScripts (you’ll need to get these CmdLets from SQLPSX)


Now it is time to create the SQL WMI Alert to monitor the arrival of this file in the FTP folder, based on our WQL :


Now let’s set the response to the alert to execute the IMPORTCSV job


Here is the code that creates the alert if you’d rather do it via TSQL and you already have the ID of the job that you wish to execute when the alert is fired.

And all is done . Every time that a file called Fileimport.csv is created on folder c:\FTPDownload then, 5 minutes later, (the reason for the ‘WITHIN 300’ clause in the WQL), the alert is fired and the job is then run. Why 300? Just to allow time for the file to arrive and be written to disk.

If you have some problem with the file and the routine generates a error, the job will finish with no errors even you using Try-Catch. This is because the exit code is 0. The error will be recorder on the job history if you look for it, but that job should finish with an error being flagged to SQL Server Agent.


How to solve this ? Just add the line ‘throw “Failure” ‘ into the catch block and then change the exit code to 1 :

and the Job will finish with an error :


Now that you’ve got that running, you can send an email using PowerShell or by the SQL job, informing whether the job was successful . it is up to you!

For now that is it folks! I hope you guys liked it the Posh DBA series. Some cool stuff are coming .


As usual, I cannot forget the awesome Jedi that is always helping this young Padawan and , of course, everyone that is needing help.

My good friends Ravikanth Chaganti, Shay Levy, my editor Andrew Clarke and the mysterious Sir Phil Factor (thanks for the XML part, Phil) Sir Bob Beauchemin , my brother Mark Broadbent and all people that kindly give their time and knowledge to share.

References :

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.


Tags: , , , , ,


  • Rate
    [Total: 40    Average: 5/5]

Laerte Junior is a Cloud and Datacenter Management MVP, focused on PowerShell and automation and, through through his technology blog and simple-talk articles, an an active member of the SQL Server and PowerShell community around the World. He is a skilled Principal Database Architect, Developer, and Administrator, specializing in SQL Server and PowerShell Programming and Automation. He also is PASS Virtual Chapter Mentor , Co-Lead of the PASS PowerShell Virtual Chapter and former PASS Regional Mentor for Brasil. In addition He is also a HUGE Star Wars Fan, his main skill. "May The Force be with us" You should follow him on Twitter as @LaerteSQLDBA

View all articles by Laerte Junior

  • Phil Factor

    Thanks for the mention
    Ah! fame at last. Thanks for the mention, Laerte.

    Great article, as usual, Laerte.

  • Laerte

    Hey Sir, for me you are a reference in SQL Server and also Powershell. In fact I need to deeply Thank you for your tech reviews and ideas. Nothing fairer than the minimum to Thank you for all the help.

  • kin

    Good Stuff
    Very informative and useful. Implementing some automation for my daily DBA job and this article will serve as a reference.

  • Laerte

    Hey Kin, glad you liked my friend 🙂 Thanks for the nice words

  • Edvaldo Castro

    Posh Man
    Hey Laerte,

    Congrats, Nice post with a great and very usefull content… Keept posting and sharing your knowledge…


  • Alex Rosa

    Great article, congrats.

  • Laerte

    Edvaldo and Alex, Thanks guys. I really appreciate your feedback and I am glad you guys liked it.

  • Ameena

    Disk space function code

    This is very userful and practical article.

    The link to Aaron Nelson’s function in the reference section you provided does not work. Could you please add the alternate link.


  • Laerte

    Disk Space
    Hi Ammeena, I am glad you liked it. The link to Aaron´s function is http://sqlvariant.com/2010/11/quick-blog-powershell-disk-and-mountpoint-check/
    I made some improvements as accept pipeline input and an array in the ServerName parameter, and you can download it at the top of the article in the functions.rar

  • djdevelop

    Nice article – just one question.
    At the end of the section titled "Outputting SQL Agent Job PowerShell in Job History" you describe the trick to get the output from SQL Agent PowerShell Job, but no matter how hard I squint, I can’t see the difference between the two lines.


  • Laerte

    djdevelop you’re right, I believe it was a some edition issues .I’ll get in touch with my Editor to fix. Thanks Man

  • Rudy

    Power Shell in SQL Server
    Very nice article.

    I would question the use of xp_cmdshell as this is a security hole. Most companies I know of don’t all the use of xp_cmdshell.

    Other than that, good job.

  • Paul Brewer

    Great article..

    If you switch the query analyser windows to SQLCMD Mode, you can type the following:

    !!PowerShell.exe -noprofile Get-Service

    It’s the equivalent of

    xp_cmdshell ‘PowerShell.exe -noprofile Get-Service’

    and works without changing default server configuration settings to allow xp_cmdshell.


  • Paul Brewer

    Great article..

    If you switch the query analyser windows to SQLCMD Mode, you can type the following:

    !!PowerShell.exe -noprofile Get-Service

    It’s the equivalent of

    xp_cmdshell ‘PowerShell.exe -noprofile Get-Service’

    and works without changing default server configuration settings to allow xp_cmdshell.


  • Jeff Moden

    @Paul Brewer,
    That’s nice… how would you run such a thing from a scheduled job?


    I’m not sure why anyone would think that xp_CmdShell is a security issue. The only people that can use it are the same ones that can turn it on and those people must have "SA" or "Control Server" privs. That includes any potential attacker who will have software that turns it on in the blink of an eye. If somneone can’t get in as "SA", they can’t use xp_CmdShell even if it’s on.

    xp_CmdShell isn’t a security issue. Bad security that allows the wrong people in as "SA" is the issue.

    I actually think that turning off xp_CmdShell is a "worst practice" because it lulls people into a false sense of security.


    Excellent article and my hat’s off to you. Keep it up!

  • Jeff Moden

    Double Post
    I’m not sure why this site double posted for me. I only hit the "Post Comment" button once.

  • mdspa

    running scripts that import modules
    I created some scripts that use the Posh-SSH module so i can collect Postgres info in my environment and import them into my repository server. Runs great in ISE but when I schedule them in the SQL Agent they fail. I created a proxy account to run the job with my credentials as a workaround. How do I get the agent to import these modules so the script will run successfully?


  • mdspa

    running scripts that import modules
    I created some scripts that use the Posh-SSH module so i can collect Postgres info in my environment and import them into my repository server. Runs great in ISE but when I schedule them in the SQL Agent they fail. I created a proxy account to run the job with my credentials as a workaround. How do I get the agent to import these modules so the script will run successfully?