• Article

Tip: Use New Cmdlets in SQL Server PowerShell to Mange SQL Server 2008

When you are working with the SQL Server PowerShell, the available cmdlets are different than when you are working with the standard Windows PowerShell. The reason for this is that the set of registered snap-ins is different. Additional SQL Server–specific cmdlets are available, and some standard PowerShell cmdlets might not be available.

In the original implementation of SQL Server PowerShell, the following additional cmdlets are included:

  • Convert-UrnToPath Converts a SQL Server Management Object Uniform Resource Name (URN) to a SQL Server provider path. The URN indicates a management object’s location within the SQL Server object hierarchy. If the URN path has characters not supported by PowerShell, the characters are encoded automatically.
  • Decode-SQLName Returns an unencoded SQL Server identifier when given an identifier that has been encoded.
  • Encode-SQLName Encodes special characters in SQL Server identifiers and name paths to formats that are usable in PowerShell paths. The characters encoded by this cmdlet include \:/%<>*?[]|. If you don’t encode these characters, you must escape them using the single quote (') character.
  • Invoke-PolicyEvaluation Evaluates management policies applied to SQL Server instances. By default, this command reports compliance but does not enforce compliance. To enforce compliance, set –AdHocPolicyEvaluationMode to Configure.
  • Invoke-SQLCmd Runs a Transact-SQL or XQuery script containing commands supported by the sqlcmd utility. By default, this cmdlet doesn’t set any sqlcmd variables by default or return message output. (Many sqlcmd commands aren’t supported.)

As the set of available cmdlets and cmdlet options change as new versions of SQL Server PowerShell are released, you can use the following techniques to discover new cmdlets and determine how they are used:

  • To view a list of all cmdlets, type get-command at the shell prompt.
  • To get detailed information about a cmdlet, type get-help CmdletName –detailed where CmdletName is the name of the cmdlet you want to examine.
  • To get detailed information about the SQL Server provider, which provides the SQL Server functionality for PowerShell, type get-help sqlserver | more.

From the Microsoft Press book Microsoft SQL Server 2008 Administrator's Pocket Consultant.

Looking for More Tips?

For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.

For more Tips on other products, visit the TechNet Magazine Tips index.