Getting Data out of SQL Server from Powershell

The preferred method to execute a query from a .NET language including Powershell is to use ADO.NET which requires creating a DataSet, then creating a DataAdapter, and finally filling the DataAdapter. For most data retrieval needs from scripts ADO.NET can be a little too heavy. Fortunately there are several ways to make this task simpler and still retain the benefits of .NET DataTables. Let's look at three methods to get SQL Serer data from Powershell. Note: You will need SMO installed which is included with SQL Server Management Studio.

Method 1: SMO ExecuteWithResults

The SMO method ExecuteWithResults returns a DataSet object representing a collection of DataTables. This method is much easier to use than ADO.NET. For example, the following code creates a database object on the pubs database and executes a query from Powershell:

The CodePlex project SQL Server Powershell Extensions (SQLPSX) provides a simple function called Get-SqlData. The function is a variation of Method 1 with error handling and some modularization. The following example executes the SQLPSX Get-SqlData function.

Get-SqlData "Z002\SqlExpress" pubs "SELECT * FROM dbo.authors"

SQLPSX also provides a Set-SqlData function for running statements which do not return data sets i.e. updates, inserts, deletes, etc.

Pros: Simple syntax, implements some basic error checking

Cons: Requires sourcing SQLPSX Library

Recommendation: Use Get-SqlData or write your own function when you want to implement a solution not dependent on the SQL 2008 Powershell host and you want to abstract some of the complexity of Method 1.

Things to do with a DataTable

Once you have your data into a DataTable you can do a number of things including piping the output to one of the built-in cmdlets to produce an html report or csv file. For example, the following code executes a query and produces nicely formatted HTML output:

Now execute your new script passing in the server and database name to get the number of days since the last database backup:

./lastbackup.ps1 'Z002\SqlExpress' pubs

As demonstrated in the article SQL Server PowerShell Extensions (SQLPSX) Part 1 you can use Get-SqlData or any of the three methods to get a list of SQL Server instances from a table and pipe the output to additional commands. For example, the following code obtains a list of SQL Server instances from a table and then retrieves version information:

The DataTable method is such a useful technique you may find using Powershell scripts to copy data from one identical table to another rather than using something as heavy weight as SSIS to be your new preferred method.

Conclusion

This article demonstrated three methods you can use to query SQL Server data from Powershell. These methods produce a DataTable as output which be combined with built-in cmdlets, piped to additional commands and used with the SqlBulkCopy class. Using one of the three methods you should be able to easily query tables from Powershell and build scripts.