Microsoft® SQL Server® 2012 Bible

784

Part V: Enterprise Data Management

SQL Cmdlets

The SQL Server PowerShell module also provides 24 new (in addition to the previous 5)

cmdlets specifi c for use with SQL Server. Although this is obviously more than SQL Server

2008 offered, the majority of administrative functions are managed using SMO, and data

access is managed using ADO.NET, as mentioned before, which is why the SQLPSX CodePlex

project is so vital to everyday DBA tasks.

To see a list of all cmdlets the SQL Server 2012 provides, fi rst import the SQLPS module and

then run Get-Command -Module SQLPS.

The most useful cmdlet, Invoke-Sqlcmd, takes query text and sends it to SQL Server for

processing. Rather than set up the structures in ADO.NET to execute queries, the

Invoke-Sqlcmd cmdlet returns results from a query passed in as a parameter or from a

text fi le, which provides an easy way to get data out of SQL Server. It can perform either a

standard Transact-SQL query or an XQuery statement, which provides additional fl exibility.

Two new cmdlets introduced with SQL Server 2012 are Backup-SqlDatabase and

Restore-SqlDatabase. These are a great alternative to the arduous task to back up a

database when you must use the SMO directly. They also have the capability to produce

a T-SQL script instead of actually backing up the databases. In general, these cmdlets

are only useful when combined as a single step in a larger process that originates from

PowerShell. (The authors don’t use PowerShell for nightly backups.)

The Invoke-PolicyEvaluation cmdlet uses the Policy-based Management feature of

SQL Server. It evaluates a set of objects against a policy defi ned for one or more servers to

determine whether the objects comply with the conditions defi ned in the policy. It can also

be used to reset object settings to comply with the policy, if that is needed. Lara Rubbelke

has a set of blog posts on using this cmdlet at http://sqlblog.com/blogs/

lara_rubbelke/archive/2008/06/19/

evaluating-policies-on-demand-through-powershell.aspx.

The character set used by SQL Server has a number of confl icts with the character set

allowed by PowerShell. For example, a standard SQL Server instance name is SQLTWSS\

INST01. The backslash embedded in the name can cause PowerShell to infer a fi le system

directory and subdirectory because it uses that character to separate the elements of the

fi le system. The Encode-SqlName cmdlet converts strings acceptable to SQL Server into

strings acceptable by PowerShell. For example, the instance name SQLTWSS\INST01 would

be converted by this cmdlet into SQLTWSS%5CINST01.

The Decode-SqlName cmdlet does the exact opposite of Encode-SqlName: It converts the

PowerShell-acceptable string of SQLTWSS%5CINST01 back to SQLTWSS\INST01.

Because SMO uses Uniform Resource Names (URN) for its objects, a cmdlet is provided to con-

vert those URN values to path names, which can be used in a Set-Location cmdlet — for

c30.indd 784c30.indd 784 7/31/2012 9:46:22 AM7/31/2012 9:46:22 AM

http://www.it-ebooks.info