PowerShell – Querying SQL Server From Command Line

PowerShell is quite an interesting capability that is added as part of Windows. I wonder how many of us really appreciate the power of this tool being out of the box. The more I see it being used in customer locations, the more I get intrigued by what it can do. Let us learn about Querying SQL Server From Command Line via powershell.

In a recent consulting assignment, I saw the development team use interesting methodology to do their code deployment. They were using PowerShell scripts to actually do the code deployment on various servers. While they were moving from one environment to another like from Dev to Test to Integration of Production, they were changing just a single parameter.

The whole thing got me interested. I asked how they were able to get this achieved. What happens to the environment specific variables, values? The Dev guy turns around and told me – “Pinal, everything is in safe hands of SQL Server”. As I stared at him with amusement, he opened up an Config Database and started showing how the trivial (though critical) values are stored in the database. They were using PowerShell to actually query these values and use it effectively in their script.

Watching the script I saw them using: Invoke-SqlCmd. This is when I started to see how powerful PowerShell scripts can be made using some out-of-box tools even without installing SQL Server on a box. I created a small script below to show how we can query a DMV and the output is put in the window.

Invoke-SqlCmd -Query "Select * from sys.databases" -ServerInstance "." 

PowerShell - Querying SQL Server From Command Line QueryDB-PowerShell-01

As you can see, the output is like what you would see inside an SSMS but in a property and value mode. This is repeated for all the databases inside the test instance of mine.

Have you ever had an opportunity to build such scripts in production environments? I would love to learn from you on how you maximized the use of Invoke-SqlCmd in your environment. Please use the comments below to let me know. Is this something new for you?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Command Line, Powershell, SQL Scripts, SQL Server
Previous Post
SQL SERVER 2016 – Management Studio Highlighting Current Line Option
Next Post
SQL SERVER – FIX: Error 1807 Could not obtain exclusive lock on database ‘model’. Retry the operation later – Part 2

Related Posts

2 Comments. Leave new

  • Jerry Cutshaw
    May 25, 2016 5:23 pm

    You have piqued my interest on how this development team performed its code releases but haven’t quite “fleshed it out” in your article. Could you be more specific how they were deploying code by querying a database and “changing one variable”?

  • edisonsullivan
    May 26, 2016 9:34 pm

    Our team has employed PowerShell to handle a multitude of SQL Server processing commands – and all without the SQLPS module that the Invoke-SqlCmd cmdlet is built into. Instead, we’ve tapped into the SqlConnectionStringBuilder object. So far we have it executing not only run-of-the-mill UPDATE, INSERT INTO, DELETE, and SELECT transactions but also have it creating and restoring backups and executing .sql files. In essence, we’re using PS like we would dynamic SQL; unlike dynamic SQL however, PS is much more powerful and is easier to maintain.


Leave a Reply