Как выполнить SQL запрос к MSSQL Server из PowerShell?

В этой статье мы рассмотрим все рабочие способы подключения к SQL Server и выполнения SQL запросов из PowerShell. Обычно такая задача стоит перед системными администраторами, которые занимаются написанием скриптов и автоматизацией на PowerShell.

Есть много разных способов работы с SQL Server через PowerShell, и глядя на статьи в интернете в них легко запутаться, потому что в разных статьях описаны разные способы, и даже у опытного администратора может возникнуть вопросы.

T-SQL запросы в PowerShell через System.Data.OleDb

Так как PowerShell имеет доступ к классам .NET, то для выполнения T-SQL можно использовать классы, находящиеся в System.Data.OleDb.

Пример PowerShell скрипта с использованием System.Data.OleDb. Выполним SELECT запрос к таблице в базе данных MS SQL:

$dataSource = “serverinstance”
$database = “master”
$sql = “SELECT * FROM sysdatabases”
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; “ +
“Initial Catalog=$database; “ +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString

$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()
$rows=($dataset.Tables | Select-Object -Expand Rows)
echo $rows

Пример PowerShell скрипта для выполнения INSERT/UPDATE/DELETE запроса к базе MSSQL:

$dataSource = “serverinstance”
$database = “test”
$sql = "insert into test_table (test_col) Values ('Test')"
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; ” +
“Initial Catalog=$database; ” +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$command = New-Object data.OleDb.OleDbCommand $sql
$command.connection = $connection
$rowsAffected = $command.ExecuteNonQuery()

Переменная $rowsAffected содержит в себе количество добавленных или измененных строк. Чтобы выполнить update или delete запрос, нужно просто изменить строку SQL запроса в переменной $sql.

SQL запрос в PowerShell к MSSQL через System.Data.SqlClient

Как и в случае с OleDb для обращения к SQL Server через System.Data.SqlClient, мы используем встроенные классы .NET. Пример SELECT запроса в скрипте PowerShell:

$server = "serverinstance"
$database = "Test"
$sql = "select * from test_table"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sql
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

Пример INSERT/DELETE/UPDATE запроса:

$server = "serverinstance"
$database = "Test"
$sql = "insert into test_table (test_col) Values ('Test')"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sql
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$rowsAffected = $SqlCmd.ExecuteNonQuery();
$SqlConnection.Close()

SQL запрос в PowerShell через модуль SQL Server Management Studio

Для использования классов Microsoft.SqlServer.Smo (SMO), в системе должна быть установлена SQL Server Management Studio.

Загружаем модуль SMO и создаём новый объект сервера, затем выполняем SELECT запрос:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "devsrvdevsrv"
$results = $serverInstance.Databases['test'].ExecuteWithResults('select * from test_table')
foreach ($res in $results.Tables) {
$nbsp;echo $res
}

Для insert/update/delete запрос выполняем ExecuteNonQuery:

$db = $serverInstance.Databases['test']
$db.ExecuteNonQuery("insert into test_table (test_col) Values ('Test555')")

Командлет Invoke-Sqlcmd из модуля SQLServer для PowerShell

Для работы с командлетом Invoke-Sqlcmd нужно установить модуль SqlServer для PowerShell. Запустите PowerShell с правами администратора и введите

Install-Module -Name SqlServer

(Несколько раз примите уведомления инсталлятора, нажав Y и enter).

После установки можно проверить что модуль корректно установился, набрав:

Get-Module SqlServer -ListAvailable

Командлет Invoke-Sqlcmd более простой и интуитивный в использовании чем другие способы подключения к SQL Server. Invoke-Sqlcmd использует один и тот же синтаксис для SELECT и INSERT/UPDATE/DELETE запросов.

Пример SELECT запроса:

Invoke-Sqlcmd -ServerInstance "serverinstance " -Query "sp_who"

Пример INSERT запроса:

Invoke-Sqlcmd -ServerInstance "serverinstance" -Database "test" -Query "insert into test_table (test_col) Values (‘Test123’)"

В отличие от других способов, запрос в Invoke-Sqlcmd всегда задается в параметре
–Query
.

Какой вариант подключения к SQL выбрать?

Выбирать между oledb/smo/sqlclient/invoke-sqlcmd нужно с учетом задачи которая перед вами стоит, и в зависимости от окружения, где планируется выполнять скрипт.

Если вы собираетесь распространять скрипт (например, ваш скрипт локально собирает данные для мониторинга) на множество серверов, то варианты c использованием SMO и плагина SqlServer (invoke-sqlcmd) стоит рассматривать в последнюю очередь, так как для отработки скрипта нужно будет устанавливать дополнительные пакеты в систему, чего хотелось бы избежать, при большом количестве серверов.

В свою очередь модуль SqlServer для PowerShell предоставляет множество других командлетов для работы с SQL Server (можно ознакомиться тут https://docs.microsoft.com/ru-ru/powershell/module/sqlserver/). Этот модуль содержит больше всего команд для администрирования самого SQL Server.

Если ваш скрипт будет выполнять не административные задачи (а, например, отвечать за какую-то часть бизнес-логики), то стоит рассмотреть System.Data.SqlClient/SMO, так как они предоставляют более удобные инструменты для разработки. Плюс OleDb в том, что он может работать не только с SQL Server, но и, например, с Access.