SQL Server PowerShellSQL Server PowerShell

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

安装 SQL Server PowerShellInstall SQL Server PowerShell

SQL Server PowerShell 模块有两种;SqlServerSQLPSThere are two SQL Server PowerShell modules; SqlServer and SQLPS.

SqlServer 模块是当前要使用的 PowerShell 模块。The SqlServer module is the current PowerShell module to use.

虽然 SQL Server 安装附带了 SQLPS 模块(用于实现后向兼容性),但该模块不再更新。The SQLPS module is included with the SQL Server installation (for backward compatibility) but is no longer updated.

SqlServer 模块不仅包含 SQLPS 更新版本的 cmdlet,还包含新的 cmdlet 以支持最新的 SQL 功能 。The SqlServer module contains updated versions of the cmdlets in SQLPS and includes new cmdlets to support the latest SQL features.

虽然 SQL Server Management Studio (SSMS) 随附了以前版本的 SqlServer 模块,但仅限 16.x 版本的 SSMS。Previous versions of the SqlServer module were included with SQL Server Management Studio (SSMS), but only with the 16.x versions of SSMS.

要在 SSMS 17.0 和更高版本中使用 PowerShell,则必须从 PowerShell 库安装 SqlServer 模块。To use PowerShell with SSMS 17.0 and later, install the SqlServer module from the PowerShell Gallery.

你也可以将 PowerShell 和 Azure Data Studio 结合使用。You can also use PowerShell with Azure Data Studio.

为什么模块从 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.

若导入 SQLPS 模块,请更新 PowerShell 脚本。Update your PowerShell scripts if you import the SQLPS module.

如果具有任何运行 Import-Module -Name SQLPS 的 PowerShell 脚本,并希望利用新的提供程序功能和新的 cmdlet,则必须将它们更改为 Import-Module -Name SqlServerIf 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. 可通过使用 #NOSQLPS 将新模块与 SQL 代理作业步骤一起使用。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. 然后,你可以使用熟悉的命令(如 cddir ),按照在命令提示符窗口中导航文件夹的类似方式导航路径。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. 可以使用其他命令(如 rendel)对路径中的节点执行操作。You can use other commands, such as ren or del, to perform actions on the nodes in the path.

  • 一组 cmdlet,它支持运行包含 Transact-SQL 或 XQuery 语句的 sqlcmd 脚本等操作。A set of cmdlets that support actions such as running a sqlcmd script containing Transact-SQL or XQuery statements.

  • AS 提供程序和 cmdlet,它们之前是单独安装的。The AS provider and cmdlets, which before they were installed separately.

SQL Server 版本SQL Server versions

SQL PowerShell cmdlet 可用于管理 Azure SQL 数据库、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.

包含 PowerShell 路径中不支持的字符的 SQL Server 标识符SQL Server identifiers that contain characters not supported in PowerShell paths

Encode-SqlnameDecode-Sqlname cmdlet 帮助你指定包含 PowerShell 路径中不支持的字符的 SQL Server 标识符。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 PowerShellFor more information, see SQL Server Identifiers in PowerShell.

使用 Convert-UrnToPath cmdlet 将数据库引擎对象的统一资源名称转换为 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 PathsFor 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 NamesFor 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. 因此,具有 PowerShell 类型作业步骤的 SQLServer 代理作业使用 SQLPS 模块。As such, SQL Server Agent jobs, which have PowerShell type job steps use the SQLPS module. 有关详细信息,请参阅如何使用 SQL Server 代理运行 PowerShellFor 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 代理作业将运行安装在计算机上的 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.

如果要在 SQL 代理作业步骤中使用 SqlServer 模块,可以将此代码放在脚本的前两行。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.

#NOSQLPS
Import-Module -Name SqlServer

Cmdlet 参考Cmdlet reference

后续步骤Next steps