Query SQL Server with PowerShell: a step-by-step guide

Automating data extraction from a SQL Server database with PowerShell saves time and removes repetitive manual work: in just a few lines you can connect to the server, run a query, see the result on screen, and save it to a reusable file. Unlike opening SQL Server Management Studio and copying values by hand, a script is repeatable, schedulable, and easy to share. This guide shows, step by step, how to query SQL Server with PowerShell using the Invoke-Sqlcmd cmdlet, from the initial connection to exporting to CSV. It is a practical skill for anyone who works with data and wants reliable routines.

Prerequisites

• Windows with PowerShell 5.1 or PowerShell 7+.
• An accessible SQL Server instance (local or remote) and read permissions on a database.
• Internet access to install the SqlServer module from the PowerShell Gallery.
• Basic SQL knowledge: the SELECT statement is enough to start.

Step 1: Install the SqlServer module

The Invoke-Sqlcmd cmdlet is part of the SqlServer module, available on the PowerShell Gallery. Open PowerShell and install it just for your user, without needing administrator privileges:

Install-Module -Name SqlServer -Scope CurrentUser
Import-Module SqlServer

On the first install from the gallery, PowerShell may warn that the PSGallery repository is untrusted; confirm the operation to continue. To confirm the cmdlet is available, run Get-Command Invoke-Sqlcmd and check that it returns the command without errors.

Step 2: Run your first query

With the module loaded, you can connect to the instance and run a query. The following example uses Windows integrated authentication (your current account) and uses splatting, a table of parameters, to keep the code readable:

$params = @{
    ServerInstance = "localhost"
    Database       = "AdventureWorks2022"
    Query          = "SELECT TOP (10) Name, ListPrice FROM Production.Product ORDER BY ListPrice DESC;"
}

$resultado = Invoke-Sqlcmd @params
$resultado | Format-Table -AutoSize

Replace ServerInstance with the name of your instance and Database with the database to query. The result is stored in the $resultado variable, where each row is an object with properties: you can filter it with Where-Object or pick columns with Select-Object without querying the server again. Storing the data in an object avoids unnecessary round trips and makes the script faster.

Step 3: SQL authentication and the certificate error

If the instance uses SQL authentication (user and password) instead of the Windows account, provide the user and the password as well. Recent versions of the SqlServer module encrypt the connection by default, so with self-signed certificates the error The certificate chain was issued by an authority that is not trusted is common. In test environments, enable TrustServerCertificate to proceed:

$ligacao = @{
    ServerInstance         = "servidor.exemplo.local"
    Database               = "Vendas"
    Username               = "leitor_bi"
    Password               = "P@ssw0rd!"
    Query                  = "SELECT COUNT(*) AS Total FROM dbo.Encomendas;"
    TrustServerCertificate = $true
}

$resultado = Invoke-Sqlcmd @ligacao

Avoid writing the password directly in the script. Prefer Get-Credential to request it securely, or a secrets vault such as Azure Key Vault in production.

Step 4: Export the results to CSV

Because each returned row is a PowerShell object, exporting to CSV is immediate with Export-Csv. Use -NoTypeInformation to skip the metadata line and -Encoding UTF8 to preserve accents and special characters:

$resultado | Export-Csv -Path "produtos.csv" -NoTypeInformation -Encoding UTF8

The produtos.csv file lands in the current folder and is ready to feed a Power BI report, a scheduled ETL process, or a simple share with colleagues who prefer spreadsheets.

Verify the result

To confirm everything worked, check the number of rows returned and read the generated file again:

"Total: $($resultado.Count)"
Import-Csv "produtos.csv" | Select-Object -First 5

If you see the first rows with the expected data, the query and the export worked. If you get a connection error, confirm that the instance is reachable on port 1433 with Test-NetConnection -ComputerName "servidor" -Port 1433 and review the ServerInstance name.

Conclusion

In a few minutes you turned a manual query into a reusable script, ready to schedule in Task Scheduler or an Azure Automation Runbook so it runs on its own every morning. From here, try parameterizing the query with variables, combining several results into a single report, or emailing the CSV automatically. Which query do you run often and would like to automate next?