SQL Server PowerShellSQL Server PowerShell

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

安裝 SQL Server PowerShellInstall SQL Server PowerShell

注意

有兩個 SQL Server PowerShell 模組:SqlServerSQLPSThere are two SQL Server PowerShell modules; SqlServer and SQLPS. SQLPS 模組隨附於 SQL Server 安裝 (基於回溯相容性),但不再更新。The SQLPS module is included with the SQL Server installation (for backwards compatibility), but is no longer being updated. 最新版 PowerShell 模組是 SqlServer 模組。The most up-to-date PowerShell module is the SqlServer module. SqlServer 模組包含 SQLPS 中 Cmdlet 的更新版本,此外還加入新的 Cmdlet 以支援最新版 SQL 功能。The SqlServer module contains updated versions of the cmdlets in SQLPS, and also 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. 若要搭配 SSMS 17.0 和更新版本使用 PowerShell,則必須從 PowerShell 資源庫安裝 SqlServer 模組。To use PowerShell with SSMS 17.0 and later, the SqlServer module must be installed from the PowerShell Gallery. 若要安裝 SqlServer 模組,請參閱安裝 SQL Server PowerShellTo install the SqlServer module, see Install SQL Server PowerShell.

為什麼模組未從 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, as well as 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 they 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 變數。Therefore, you do not 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.

SQL Server Agent 所使用的模組沒有任何變更。There is no change to the module used by SQL Server Agent. 因此,PowerShell 類型的作業步驟會使用 SQLPS 模組。Therefore, job steps of the type PowerShell use the SQLPS module. 如需詳細資訊,請參閱如何使用 SQL Server Agent 執行 PowerShellFor more information, see How to run PowerShell with SQL Server Agent.

SQL Server PowerShell 元件SQL Server PowerShell Components

SqlServer 模組載入兩個 Windows PowerShell 嵌入式管理單元:The SqlServer module loads two Windows PowerShell snap-ins:

  • SQL ServerSQL Server 提供者,可啟用類似於檔案系統路徑的簡單導覽機制。A SQL ServerSQL Server provider, which enables a simple navigation mechanism similar to file system paths. 您可以建立類似於檔案系統路徑的路徑,其中的磁碟機與 SQL ServerSQL Server 管理物件模型有關聯,而且節點是根據物件模型類別。You can build paths similar to file system paths, where the drive is associated with a SQL ServerSQL 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-SQLTransact-SQL 或 XQuery 陳述式的 sqlcmd 指令碼。A set of cmdlets that support actions such as running a sqlcmd script containing Transact-SQLTransact-SQL or XQuery statements.

SQL Server 版本SQL Server Versions

SQL PowerShell Cmdlet 可用來管理 Azure SQL Database、Azure SQL 資料倉儲及所有支援的 SQL Server 產品執行個體。SQL PowerShell cmdlets can be used to manage instances of Azure SQL Database, Azure SQL Data Warehouse, 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 將 Database EngineDatabase Engine 物件的唯一資源名稱轉換為 SQL Server PowerShell 提供者的路徑。Use the Convert-UrnToPath cmdlet to convert a Unique Resource Name for a Database EngineDatabase 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 enumerate 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.

Cmdlet 參考Cmdlet reference