Powershell

Oh my love affair with Powershell gets overwhelming some days.  It really is a troublesome little mistress.

For every time somebody comes up to me and asks “Can this be done in Powershell?” the answer is almost invariably “YES!”

Sometimes the answer takes a bit of thought and bit of effort.

But what I love, is once you design the answer it is almost ALWAYS Neutral of your Infrastructure or Development Environment!  You can take those solutions with you anywhere!

Today we had a question.  “Can’t we just query the SQL servers to see what instances we have?”

I’m not an SQL guy.  I can’t even pretend to be.  I can install it, I can navigate it, I can drop tables and make messes.

But I’m not a SQL guru.

But I DO know that SQL server 2008 Management tools has a Powershell Snapin.  It sits INSIDE the Management Studio but it’s there.

It’s a very Dead simple command to for Powershell people.  Just use the SQL Provider and good old “GET-CHILDITEM”

Yup.

GET-CHILDITEM SQLSERVER:\SQL\SERVERNAME\DEFAULT\Databases

 

Where “SERVERNAME” is the name of an SQL server.  And you can navigate to the different servers this way to by just changing the name.

Now the problem I ran into is if you don’t have credentials, you can’t connect (DUH).  I couldn’t find the answer yet on how to pass alternate credential in the SQLProvider in Powershell.  But I DID discover that the database list on a SQLServer (like a LOT of information in Windows) can be obtained by good old WMI.

 

So the alternate method you can ALSO use to show the Databases on a SQL Server is run a GET-WMIOBJECT against “win32_perfformatteddata_mssqlserver_sqlserverdatabases” (*Yes, it’s a mouthful*)

 

GET-WMIOBJECT win32_perfformatteddata_mssqlserver_sqlserverdatabases

 

Need the list from a foreign computer?  Just drop in the IP address or resolvable name!

 

GET-WMIOBJECT win32_perfformatteddata_mssqlserver_sqlserverdatabases –computer SERVERNAME

 

And of course like everything else in Powershell, passing credentials will validate you if necessary. 

 

GET-WMIOBJECT win32_perfformatteddata_mssqlserver_sqlserverdatabases –computer SERVERNAME –credential DOMAIN\Username

 

Now you’d like that to be readable, because WMI usually gives a LOT of information we don’t need, so just format the output to a table and pick what you need.

 

GET-WMIOBJECT win32_perfformatteddata_mssqlserver_sqlserverdatabases –computer SERVERNAME –credential DOMAIN\Username | format-table Name

See?  And I didn’t even trip on a single “Table” in the “process”.

Yes my worst Pun of the day.

 

Powershell, Enjoy the good life

Sean
The Energized Tech

Advertisements