Recently I had the need to help someone download some data from SQL Server as a part of an automated process. In this case, the person wanted to use PowerShell (PoSh), since they were performing a few other actions with PoSh as well.

This article is a short tutorial on exporting data from SQL Server with PowerShell in a few different ways.

Using Basic PowerShell

In order to download data, we need to connect to a SQL Server and then retrieve the data in some way. We then write that out to a text file. One of the advantages of using PoSh is that we have more control over how we manipulate the files.

To get data from SQL Server, we can use the Invoke-SqlServer cmdlet in the SqlServer module. This

Invoke-Sqlcmd -Query "SELECT * FROM [Sandbox].[dbo].[Customer]" -ServerInstance ".\SQL2019"

This will return some information from the database as a table.

That's useful, but what we really want to do is export this data into a file. To do that, we can pipe the output of Invoke-SqlCmd to the Export-Csv cmdlet, which will take a PoSh object and produce a CSV formatted file. There are lots of options with this cmdlet, but the only thing we really need is the filename. I'll set that in a variable and pass it into the cmdlet. We'll also then use a pipe to send the output to the same cmdlet.

$filename = "C:\Users\way0u\OneDrive\Documents\SQL\Customer"
Invoke-Sqlcmd -Query "SELECT * FROM [Sandbox].[dbo].[Customer]" -ServerInstance ".\SQL2019" |
Export-Csv -Path "$filename.csv" -NoTypeInformation

Once this runs, I'll see a file in my folder:

Opening the file up in Sublime Text, I see the contents of my table:

This is one of the easiest ways to use PoSh to get data out of a table with the basic tools. You do need to ensure the SqlServer module is installed, which is an action to take.

Using DbaTools

I think dbatools is one of the essential PowerShell modules for every Windows data professional. These are geared towards the DBA work of migrating between instances, but any sort of action you can take is covered under the wide range of cmdlets that are available. What's more, all the code that is contained in this module is available on GitHub, so if your organization has any concerns, you can just look at the code. This is really no different than you doing the work to build these cmdlets.

We won't cover installing the dbatools module, but the site does a good job of explaining this. Instead, let's look at a different way of exporting data. To do that, we'll examine two cmdlets, Get-DbaDbTable and  Export-DbaDbTableData.

Let's show a simple example. I'll extract the same data with these cmdlets, showing the syntax needed. We use Get-DbaDbTable to get the table object. We then pipe this output to Export-DbaDbTableData.

Get-DbaDbTable -SqlInstance way0utwesthp\SQL2019 -Database Sandbox -Table customer | '
Export-DbaDbTableData -FilePath C:\Users\way0u\OneDrive\Documents\SQL\Customer2.sql

Note that we are exporting a .sql script. When I do this, I get these results inside of the file:

This is T-SQL code, designed to insert data into the same table, in the same database. Essentially, this is a migration script for data.

While this doesn't give you an export you can use in Excel, it is a handy way to get data out that you might want to move to another instance of SQL Server, without dealing with the overhead of SSIS or one of the export wizards in your IDE. What's more, this can be scripted and included with other types of code movement, like migrating the table itself.

I can even pass in multiple tables and get a combined script. If I use this code:

Get-DbaDbTable -SqlInstance way0utwesthp\SQL2019 -Database Sandbox -Table 'dbo.Customer','dbo.Shipper' | 
Export-DbaDbTableData -FilePath C:\Users\way0u\OneDrive\Documents\SQL\Combined.sql -Append

I see this in the combined.sql file:

This isn't quite what many of us think about an export, but this is one way to get the data out of a table if the intention is to put it back into another table. Of course, if the new table is a different database, there might be a need to edit the file programmatically to fix the "USE" statement.

Conclusion

There are a variety of ways to get data from a SQL Server table into a file, and I've shown you a few methods that work with PowerShell. Of course, bcp is also available from within Powershell with the Invoke-Expression cmdlet, but this likely is less convenient than just using the cmdline version if you need to export a single table.

Powershell shines, however, when there are more complex tasks, such as manipulating strings to produce different file names, handling multiple tables dynamically, and even performing other tasks like moving files to different locations. While this can be done in normal batch scripting, or with xp_cmdshell, these tasks are easier, and the code is cleaner in PowerShell, at least, I would argue that's the case.

Keep in mind that when working with objects this way, these are stored in memory, and there could be performance issues with very large amounts of data. For very high data volumes, bcp.exe or a specialized tool designed for high loads are likely your best choice.