Have you ever needed to download data from SQL Server as part of an automated process? If so, PowerShell (PoSh) can be a great tool to accomplish this task. In this article, we will explore different ways to export data from SQL Server using PowerShell.

Using Basic PowerShell

To retrieve data from SQL Server using PowerShell, we can utilize the Invoke-SqlServer cmdlet in the SqlServer module. This cmdlet allows us to execute SQL queries and retrieve the results as a table.

For example, the following code retrieves all data from the “Customer” table in the “Sandbox” database:

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

Once we have the data, we can export it to a file using the Export-Csv cmdlet. This cmdlet takes a PowerShell object and produces a CSV formatted file.

Here is an example of exporting the data to a CSV file:

$filename = "C:\Users\way0u\OneDrive\Documents\SQL\Customer"
$data | Export-Csv -Path "$filename.csv" -NoTypeInformation

After running this code, you will find a file in the specified folder containing the data from the table.

Using DbaTools

If you are a Windows data professional, you may already be familiar with the dbatools module. This PowerShell module provides a wide range of cmdlets for database administration tasks, including exporting data from SQL Server.

To export data using dbatools, we can use the Get-DbaDbTable and Export-DbaDbTableData cmdlets. The Get-DbaDbTable cmdlet retrieves the table object, which can then be piped to the Export-DbaDbTableData cmdlet to export the data.

Here is an example of exporting data using dbatools:

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

In this example, the data is exported as a .sql script, which can be used to insert the data into another table in the same database. This is particularly useful for data migration tasks.

Additionally, you can export data from multiple tables and combine them into a single script using the same approach:

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

By leveraging the power of dbatools, you can easily export data from SQL Server and perform complex tasks such as manipulating strings, handling multiple tables dynamically, and moving files to different locations.

Conclusion

Exporting data from SQL Server using PowerShell provides flexibility and control over the exported data. Whether you choose to use basic PowerShell or the dbatools module, PowerShell offers a convenient way to export data from SQL Server tables.

Keep in mind that when working with large amounts of data, performance issues may arise. In such cases, specialized tools like bcp.exe or other high-load tools may be more suitable.

Overall, PowerShell is a powerful tool for exporting data from SQL Server, and it can simplify complex tasks while keeping the code clean and manageable.