SQL Server 2008 CTP6 gives you PowerShell

The sixth CTP of SQL Server 2008, made available this past week at http://connect.microsoft.com/sql provides a PowerShell provider for SQL Server, which is backwards compatible with SQL Server 2005 as well. I showed it a little at the User Group in Melbourne a few days ago, and some people seemed to like it.

powershell_sql Basically, you can now open up PowerShell, and change directory to the PowerShell drive "SQL:". Then change directories through the instances, databases, tables, and so on. At any point, try something like "dir | gm" (gm is Get-Member, dir is an alias for Get-ChildItem), to find out what properties and methods are available on the objects in that folder.

Whilst T-SQL will probably remain the preferred environment for many, this scripting seems to be a step up from using SMO through PowerShell. However, it seems to be just a wrapper for SMO - when I tried to find out information about the Script() method on tables, it turns out to take a parameter of type Microsoft.SqlServer.Management.Smo.ScriptingOptions - I found this a little disappointing, and I haven't figured out the best way of getting help on methods that are on these objects either.

It's definitely a good start though - should be a very nice feature of SQL Server 2008.

Published Sat, Feb 23 2008 16:00 by Rob Farley
Filed under: ,

Comments

Saturday, February 23, 2008 12:57 AM by Rob Farley

# Transfer SQL Server Objects Task internals

How often do you wonder what something's actually doing under the hood (in the way that it talks

Monday, March 03, 2008 4:53 PM by Rob Farley

# Using ScriptingOptions with SQLPS

I've written before that SQL Server 2008 (February CTP) gives you SQLPS - a PowerShell interface

Sunday, October 12, 2008 9:01 PM by Siraj Jamdar

# re: SQL Server 2008 CTP6 gives you PowerShell

I find it intriguing that finally PowerShell provides an opportunity to pipe AND tee output.

One scenario I can think of is as follows. An SSIS package encounters an error. If I tee the output, relevant details can be logged. Simultaneously, based on severity, a case statement might fire off to send an SMS if it is very high priority, or it can send out an email so that the issue can be dealt with at a more sedate pace...