How to execute .sql file using powershell?

sql

sql-server

powershell

sqlcmd

byAnton Shumikhin·Oct 28, 2024

Execute a .sql file with PowerShell using:

Invoke-Sqlcmd -InputFile "script.sql" -ServerInstance "Server" -Database "DB"

Replace script.sql with your file, Server with your SQL server, and DB with your database name. Ensure the SqlServer module is installed (Install-Module -Name SqlServer).

Setting up your PowerShell Environment to Run SQL

Before launching your .sql script through PowerShell, ensure your environment:

Snap-ins Check: Use Get-PSSnapin -Registered to see if SqlServerCmdletSnapin100 and SqlServerProviderSnapin100 are there. If not, add them using Add-PSSnapin.

if (-not (Get-PSSnapin -Name SqlServerCmdletSnapin100 -Registered)) {
    # "Snap-in" to action. Get it? Snap-in! 
    Add-PSSnapin SqlServerCmdletSnapin100
}

SQLPS Module for Later Versions: For SQL Server versions 2012 onwards, use the SQLPS or SqlServer module. Everything depends on the version you are having a date with.
```
# Starting the module magic now!
Import-Module SqlServer
```
2008 Snap-ins: If you are dealing with SQL Server 2008, snap-ins might be your only allies. Take up the effort to import them from the PowerShell Modules directory.
Go Direct with SqlConnection: As a direct and custom approach, use System.Data.SqlClient.SqlConnection along with the SqlCommand class.

Executing with a Side of Control and Handling

Let's enhance and tune the command execution:

Fine-tuning Execution

Various parameters provide more control. Queue endless possibilities!

QueryTimeout: Save your time or limit the script's time. Whoever comes first!
ErrorAction 'Stop': Call it a day when there's an error.
Verbose: Get yourself a detailed version of what's happening.

Mastering Error Management

Make use of try/catch/finally blocks because you have the power!

try {
    Invoke-Sqlcmd -InputFile "C:\path\to\script.sql" -ServerInstance "Server"
} catch {
    Write-Host "Looks like we've hit a bump: $_"
} finally {
    Write-Host "Done for now, phew..."
}

Minimum but Efficient

Consider a non-module SQL execution:

$conn = New-Object System.Data.SqlClient.SqlConnection "ConnectionString"
# Time to open up!
$conn.Open()
$cmd = $conn.CreateCommand()
# Loading, please wait...
$cmd.CommandText = (Get-Content "script.sql" -Raw)
# Take the leap, my command!
$cmd.ExecuteNonQuery()
# Till next time!
$conn.Close()