SQL Server PowerShellSQL Server PowerShell

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure 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 DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure 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.

SQLPS 模組隨附於 SQL Server 安裝 (用於回溯相容性),但不再更新。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.

舊版 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 及更新版本,請從 PowerShell 資源庫 安裝 SqlServer 模組。To use PowerShell with SSMS 17.0 and later, install the SqlServer module from the PowerShell Gallery.

您也可以使用 PowerShell 搭配 Azure Data StudioYou 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.

如果 SQLPS 模組安裝在同一部電腦上,則建議使用 Import-Module SQLServer 啟動指令碼,以避免發生並存問題。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 Agent) 執行的指令碼。This section applies to scripts executed from PowerShell and not the SQL Agent. 新模組可與使用 #NOSQLPS 的 SQL Agent 作業步驟搭配使用。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 Database、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 AgentSQL Server Agent

SQL Server Agent 所使用的模組沒有任何變更。There's no change to the module used by SQL Server Agent. 因此,具有 PowerShell 類型作業步驟的 SQL Server Agent 作業會使用 SQLPS 模組。As such, SQL Server Agent jobs, which have PowerShell type job steps use the SQLPS module. 如需詳細資訊,請參閱如何使用 SQL Server Agent 執行 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 Agent 自動載入 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 Agent 作業會執行電腦上所安裝的 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 Agent 作業步驟中使用 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