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:

sql-ps-snapin1.jpg

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

sql-ps-snapin2.jpg

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

sql-ps-snapin3.jpg

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

sql-ps-snapin41.jpg

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

sql-ps-snapin5.jpg

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

ยต