Querying a SQL Server Database from PowerShell

A few days ago, I wrote a PowerShell script on my computer that would ultimately be used on a different computer to automate a specific task. One of the things this script did was to query a SQL Server database which worked fine on my computer. After moving the script, it didn’t take long to figure out that the other computer didn’t have the necessary SQL PowerShell snap-in or module. My goal was to install the minimum features of SQL Server 2008 R2 to be able to run Transact SQL from PowerShell against a remote SQL Server.

For SQL Server 2008 R2, the “Management Tools – Basic” is all that is needed for the SQL PowerShell snap-ins to be installed:

If you’re installing from the command line, it’s the “SSMS” feature:

Performing this install from either the GUI or command line, will also automatically add the “SQL Client Connectivity SDK”:

The SQL PowerShell snap-ins are installed and available to be added via the Add-PSSnapin PowerShell cmdlet:

I’m now able to query a SQL Server database on a remote server using PowerShell:

With SQL 2012, the SQL PowerShell Snap-ins are replaced with SQL PowerShell Modules.

µ

1 Comment

  1. Justin Dearing (@zippy1981)

    Mike,

    A great article. SSMS is a large amount of overhead for Invoke-SqlCmd though. You might want to look into Invoke-SqlCmd2 (http://poshcode.org/3448). Chad Miller wrote the bulk of it, but myself and others have contributes to it as well. It does a lot of things that the Invoke-SqlCmd2 does not do. Many DBAs and many organizations do not trust third party scripts on their servers, so its not a solution for everyone, However, its certainly an alternative that works in many cases.

    Justin

    Reply

Leave a Reply

%d bloggers like this: