So you’ve decided you have to work your SQL Database with Powershell.   Honestly I never thought of doing this.  The SQL Management  Console seems to meet all my needs.

But perhaps you’ve got a legacy application that creates user ID’s and accounts in SQL.  Or your Development team wants to hand you off a minor database task but they only have SQL queries for the code?  Maybe you’re just bored?

We’ve all got our reasons to get things done.   With SQL Server 2008 we DO have the option to use Powershell, well sort of.

Somebody on the SQL team didn’t listen to somebody else and they made this “MiniShell” (which I’m not even going to get into).  What this GENERALLY means is unless you launch Powershell from the SQL Management Console or the direct executable, you’re not going to be using the SQL Cmdlets.

Granted, there are only three but damn they’re powerful and useful.

So how do we add SQL to a regular Powershell session?  Well provided you have the SQL Management Studio on a machine with Powershell you execute these two lines first

add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100

Which will give you three new Cmdlets





INVOKE-SQLCMD is my favorite since it allows me to execute an SQL Query (Translates to the ability to manipulate, Create an SQL Database) as if I was in the Management Studio.   Where this is USEFUL to the Powershell Administrator is that is returns OBJECTS just like normal in Powershell.

More importantly, I can now interface directly with the SQL instance on terms I am comfortable with and dig for stuff like “How Fragmented is this index?”

Let’s kick this up a notch.  If the SQL server is running Powershell 2.0, you could enable remoting and tie all of this into your normal Administration in Powershell or just simply MANAGE the SQL server without any Management tools on your computer at all (Other than Powershell)

So why do this? 

If you’re doing JUST SQL I personally would stick with the SQL Management Studio.  It’s perfect for that.  Where Powershell comes into play could be some maintenance scenarios or even User creation situations.  It could be (as I was muttering before) a situation where Development has Code that runs in a Web page that performs a task.   Their only code is an SQL query.    You as the Administrator can leverage that SAME code without change on your terms.

You might even be able to help out your Developer friends.

How YOU choose to use it is up to you.

The Power of Shell is in YOU

The Energized Tech