SQL Server PowerShell

Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Parallel Data Warehouse

There are two SQL Server PowerShell modules; SqlServer and SQLPS.

The SqlServer module is the current PowerShell module to use.

The SQLPS module is included with the SQL Server installation (for backward compatibility) but is no longer updated.

The SqlServer module contains updated versions of the cmdlets in SQLPS and includes new cmdlets to support the latest SQL features.

Previous versions of the SqlServer module were included with SQL Server Management Studio (SSMS), but only with the 16.x versions of SSMS.

To use PowerShell with SSMS 17.0 and later, install the SqlServer module from the PowerShell Gallery.

Why did the module change from SQLPS to SqlServer?

To ship SQL PowerShell updates, we had to change the identity of the SQL PowerShell module, and the wrapper known as SQLPS.exe. Because of this change, there are now two SQL PowerShell modules, the SqlServer module, and the SQLPS module.

Update your PowerShell scripts if you import the SQLPS module.

If you have any PowerShell scripts that run Import-Module -Name SQLPS , and you want to take advantage of the new provider functionality and new cmdlets, you must change them to Import-Module -Name SqlServer . The new module is installed to %ProgramFiles%\WindowsPowerShell\Modules\SqlServer folder. As such, you don’t have to update the $env:PSModulePath variable. If you have scripts that use a third-party or community version of a module named SqlServer, use the Prefix parameter to avoid name collisions.

It is recommended to start your script with Import-Module SQLServer to avoid side-by-side issues if the SQLPS module is installed on the same machine.

This section applies to scripts executed from PowerShell and not the SQL Agent. The new module can be used with SQL Agent job steps using #NOSQLPS.

SQL Server PowerShell Components

The SqlServer module comes with:

PowerShell Providers, which enables a simple navigation mechanism similar to file system paths. You can build paths similar to file system paths, where the drive is associated with a SQL Server management object model, and the nodes are based on the object model classes. You can then use familiar commands such as cd and dir to navigate the paths similar to the way you navigate folders in a command prompt window. You can use other commands, such as ren or del, to perform actions on the nodes in the path.

A set of cmdlets that support actions such as running a sqlcmd script containing Transact-SQL or XQuery statements.

The AS provider and cmdlets, which before they were installed separately.

SQL Server versions

SQL PowerShell cmdlets can be used to manage instances of Azure SQL Database, Azure Synapse Analytics, and all supported SQL Server products.

SQL Server identifiers that contain characters not supported in PowerShell paths

The Encode-Sqlname and Decode-Sqlname cmdlets help you specify SQL Server identifiers that contain characters not supported in PowerShell paths. For more information, see SQL Server Identifiers in PowerShell.

Use the Convert-UrnToPath cmdlet to convert a Unique Resource Name for a Database Engine object to a path for the SQL Server PowerShell provider. For more information, see Convert URNs to SQL Server Provider Paths.

Query Expressions and Unique Resource Names

Query expressions are strings that use syntax similar to XPath to specify a set of criteria that enumerates one or more objects in an object model hierarchy. A Unique Resource Name (URN) is a specific type of query expression string that uniquely identifies a single object. For more information, see Query Expressions and Uniform Resource Names.

SQL Server Agent

There’s no change to the module used by SQL Server Agent. As such, SQL Server Agent jobs, which have PowerShell type job steps use the SQLPS module. For more information, see How to run PowerShell with SQL Server Agent. However, starting with SQL Server 2019, you can disable SQLPS. To do so, on the first line of a job step of the type PowerShell you can add #NOSQLPS , which stops the SQL Agent from auto-loading the SQLPS module. When you do this, your SQL Agent Job runs the version of PowerShell installed on the machine, and then you can use any other PowerShell module you like.

If you want to use the SqlServer module in your SQL Agent Job step, you can place this code on the first two lines of your script.

Как выполнить 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 = “server\instance”
$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 = “server\instance”
$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 = «server\instance»
$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 = «server\instance»
$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()

1. Создается объект соединения с MSSQL сервером;
2. Создается объект с SQL запросом, и ему присваивается объект соединения;
3. Затем в случае выполнения SELECT запроса создается объект адаптера и в контексте этого адаптера выполняется запрос;
4. В случае выполнения INSERT/UPDATE/DELETE запроса объект с запросом (уже содержащий в себе объект соединения) выполняет метод ExecuteNonQuery() .

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’) «devsrv\devsrv»
$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’)»)

1. Скачайте nuget.exe https://www.nuget.org/downloads;
2. Запустите PowerShell с правами администратора и перейдите в директорию с файлом nugget.exe;
3. Выполните: .\nuget.exe Install Microsoft.SqlServer.SqlManagementObjects ;
   
4. В той же директории где лежит nuget.exe появится папка Microsoft.SqlServer.SqlManagementObjects со всеми нужными DLL;
5. Загрузите библиотеку SMO из DLL файла. Добавьте в ваш скрипт:

add-type –Path «C:\Users\username\Downloads\Microsoft.SqlServer.SqlManagementObjects.150.18208.0\lib\net45\Microsoft.SqlServer.Smo.dll»

После этого классы SMO станут доступны для использования.

Командлет 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 «server\instance » -Query «sp_who»

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

Invoke-Sqlcmd -ServerInstance «server\instance» -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) стоит рассматривать в последнюю очередь, так как для отработки скрипта нужно будет устанавливать дополнительные пакеты в систему, чего хотелось бы избежать, при большом количестве серверов.

SQL Server PowerShell SQL Server PowerShell

Применимо к: Applies to: SQL Server SQL Server (все поддерживаемые версии) SQL Server SQL Server (all supported versions) База данных SQL Azure Azure SQL Database База данных SQL Azure Azure SQL Database Управляемый экземпляр SQL Azure Azure SQL Managed Instance Управляемый экземпляр SQL Azure Azure SQL Managed Instance Azure Synapse Analytics Azure Synapse Analytics Azure Synapse Analytics Azure Synapse Analytics Параллельное хранилище данных Parallel Data Warehouse Параллельное хранилище данных Parallel Data Warehouse Применимо к: Applies to: SQL Server SQL Server (все поддерживаемые версии) SQL Server SQL Server (all supported versions) База данных SQL Azure Azure SQL Database База данных SQL Azure Azure SQL Database Управляемый экземпляр SQL Azure Azure SQL Managed Instance Управляемый экземпляр SQL Azure Azure SQL Managed Instance Azure Synapse Analytics Azure Synapse Analytics Azure Synapse Analytics Azure Synapse Analytics Параллельное хранилище данных Parallel Data Warehouse Параллельное хранилище данных Parallel Data Warehouse

Существует два модуля SQL Server PowerShell — SqlServer и SQLPS . There are two SQL Server PowerShell modules; SqlServer and SQLPS.

Самым актуальным модулем PowerShell является модуль SqlServer. The SqlServer module is the current PowerShell module to use.

Модуль SQLPS входит в состав установки SQL Server (для обеспечения обратной совместимости), но больше не обновляется. The SQLPS module is included with the SQL Server installation (for backward compatibility) but is no longer updated.

Модуль SqlServer содержит обновленные версии командлетов в SQLPS и новые командлеты для поддержки последних функций SQL. The SqlServer module contains updated versions of the cmdlets in SQLPS and includes new cmdlets to support the latest SQL features.

Предыдущие версии модуля SqlServer входили в состав среды SQL Server Management Studio (SSMS), но только с SSMS версий 16.x. Previous versions of the SqlServer module were included with SQL Server Management Studio (SSMS), but only with the 16.x versions of SSMS.

Для работы PowerShell с SSMS версии 17.0 и более поздних установите модуль SqlServer из коллекции PowerShell. To use PowerShell with SSMS 17.0 and later, install the SqlServer module from the PowerShell Gallery.

Почему модуль SQLPS изменился на SqlServer? Why did the module change from SQLPS to SqlServer?

Для поставки обновлений SQL PowerShell было необходимо изменить удостоверение модуля SQL PowerShell, а также программу-оболочку, известную как SQLPS.exe. To ship SQL PowerShell updates, we had to change the identity of the SQL PowerShell module, and the wrapper known as SQLPS.exe. В связи с эти изменением теперь существует два модуля SQL PowerShell — модуль SqlServer и модуль SQLPS. Because of this change, there are now two SQL PowerShell modules, the SqlServer module, and the SQLPS module.

Обновите скрипты PowerShell, если вы импортируете модуль SQLPS. Update your PowerShell scripts if you import the SQLPS module.

Если у вас есть скрипты PowerShell, которые выполняют команду Import-Module -Name SQLPS , и вы хотите использовать преимущества новых функциональных возможностей поставщика и новых командлетов, необходимо изменить их на Import-Module -Name SqlServer . If you have any PowerShell scripts that run Import-Module -Name SQLPS , and you want to take advantage of the new provider functionality and new cmdlets, you must change them to Import-Module -Name SqlServer . Новый модуль устанавливается в папку %ProgramFiles%\WindowsPowerShell\Modules\SqlServer . The new module is installed to %ProgramFiles%\WindowsPowerShell\Modules\SqlServer folder. Поэтому не нужно обновлять переменную $env:PSModulePath. As such, you don’t have to update the $env:PSModulePath variable. При наличии скриптов, использующих версию модуля с именем SqlServer, созданную сторонними производителями или сообществом, используйте параметр Prefix, чтобы имена не конфликтовали. If you have scripts that use a third-party or community version of a module named SqlServer, use the Prefix parameter to avoid name collisions.

Рекомендуется запустить сценарий с помощью Import-Module SQLServer, чтобы избежать проблем параллельной установки, если модуль SQLPS установлен на том же компьютере. It is recommended to start your script with Import-Module SQLServer to avoid side-by-side issues if the SQLPS module is installed on the same machine.

Этот раздел относится к скриптам, выполняемым из PowerShell, а не агента SQL. This section applies to scripts executed from PowerShell and not the SQL Agent. Новый модуль можно использовать с шагами задания агента SQL с помощью #NOSQLPS. The new module can be used with SQL Agent job steps using #NOSQLPS.

Компоненты SQL Server PowerShell SQL Server PowerShell Components

Компоненты модуля SqlServer: The SqlServer module comes with:

Поставщики PowerShell — предоставляет простой механизм навигации, аналогичный путям в файловой системе. PowerShell Providers, which enables a simple navigation mechanism similar to file system paths. Можно построить пути, аналогичные путям файловой системы, где диску соответствует управляющая объектная модель SQL Server, а узлы основаны на классах объектной модели. You can build paths similar to file system paths, where the drive is associated with a SQL Server management object model, and the nodes are based on the object model classes. Затем можно использовать привычные команды, такие как cd и dir , чтобы перемещаться по путям, аналогично переходу по структуре папок в окне командной строки. You can then use familiar commands such as cd and dir to navigate the paths similar to the way you navigate folders in a command prompt window. Для выполнения действий на узлах пути можно использовать другие команды, например ren или del. You can use other commands, such as ren or del, to perform actions on the nodes in the path.

Набор командлетов, которые поддерживают такие действия, как запуск скрипта sqlcmd, содержащего инструкции Transact-SQL или XQuery. A set of cmdlets that support actions such as running a sqlcmd script containing Transact-SQL or XQuery statements.

Поставщик AS и командлеты, которые ранее устанавливались отдельно. The AS provider and cmdlets, which before they were installed separately.

Версии SQL Server SQL Server versions

Командлеты SQL PowerShell можно использовать для управления экземплярами базы данных SQL Azure, Azure Synapse Analytics и во всех поддерживаемых продуктах SQL Server. SQL PowerShell cmdlets can be used to manage instances of Azure SQL Database, Azure Synapse Analytics, and all supported SQL Server products.

Идентификаторы SQL Server, содержащие символы, не поддерживаемые в путях Windows PowerShell SQL Server identifiers that contain characters not supported in PowerShell paths

Командлеты Encode-Sqlname и Decode-Sqlname помогают указать идентификаторы SQL Server, содержащие символы, не поддерживаемые в путях Windows PowerShell. The Encode-Sqlname and Decode-Sqlname cmdlets help you specify SQL Server identifiers that contain characters not supported in PowerShell paths. Дополнительные сведения см. в статье SQL Server Identifiers in PowerShell. For more information, see SQL Server Identifiers in PowerShell.

Используйте командлет Convert-UrnToPath, чтобы преобразовать уникальное имя ресурса для объекта ядра СУБД в путь для поставщика SQL Server PowerShell. Use the Convert-UrnToPath cmdlet to convert a Unique Resource Name for a Database Engine object to a path for the SQL Server PowerShell provider. Дополнительные сведения см. в статье Convert URNs to SQL Server Provider Paths. For more information, see Convert URNs to SQL Server Provider Paths.

Выражения запросов и уникальные имена ресурсов Query Expressions and Unique Resource Names

Выражения запроса — это строки, которые используют синтаксис, напоминающий XPath для указания набора условий, перечисляющих один или несколько объектов в иерархии объектной модели. Query expressions are strings that use syntax similar to XPath to specify a set of criteria that enumerates one or more objects in an object model hierarchy. Уникальное имя ресурса (URN) — это определенный тип строки выражения запроса, который уникально определяет один объект. A Unique Resource Name (URN) is a specific type of query expression string that uniquely identifies a single object. Дополнительные сведения см. в статье Query Expressions and Uniform Resource Names. For more information, see Query Expressions and Uniform Resource Names.

Агент SQL Server SQL Server Agent

Модуль, используемый агентом SQL Server, не изменился. There’s no change to the module used by SQL Server Agent. Таким образом, задания агента SQL Server, которые содержат шаги задания типа PowerShell, используют модуль SQLPS. As such, SQL Server Agent jobs, which have PowerShell type job steps use the SQLPS module. Дополнительные сведения: Запуск PowerShell с помощью агента SQL Server. For more information, see How to run PowerShell with SQL Server Agent. Однако начиная с версии SQL Server 2019 вы можете отключить SQLPS. However, starting with SQL Server 2019, you can disable SQLPS. Для этого в первой строке шага задания типа PowerShell можно добавить #NOSQLPS , чтобы агент SQL не запускал автоматическую загрузку модуля SQLPS. To do so, on the first line of a job step of the type PowerShell you can add #NOSQLPS , which stops the SQL Agent from auto-loading the SQLPS module. После этого задание агента SQL Server запустит установленную на компьютере версию PowerShell, и вы можете использовать любой другой модуль PowerShell. When you do this, your SQL Agent Job runs the version of PowerShell installed on the machine, and then you can use any other PowerShell module you like.

Если вы хотите использовать модуль SqlServer в шаге задания агента SQL, можно поместить этот код в первые две строки скрипта. If you want to use the SqlServer module in your SQL Agent Job step, you can place this code on the first two lines of your script.