Instead of working, I blog.

I was asked to write a quick blog on what makes PS so awesome. And while it's a huge topic with many many examples, I'll give you a couple just to get you going on how cool it is.

1. It makes it really easy to add things to your process that you never could before. Let's take for example that you want to make a change to your DB but you want to backup the schema for an object or a set of objects first. This is so easy in PS it's not even funny. The code to script out objects is:

gci| %{$_.Script()}

That's it!!! And all you have to do is add a filter if you want to script only a certain schema, or a certain object. And the code doesn't change if you want to script tables, views, function, SPs, etc. All you have to do is change the node of the tree you're in... and for that you manage it like DOS. So to script out a table you would type this code:

cd sql:\sqlserver\servername\default\databases\MyDB\Tables
Then just run the script code above.

AND to script out the SPs, you just change the contest to StoredProcedures like this.

cd ..
cd StoredProcedures

That will put you in:
cd sql:\sqlserver\servername\default\databases\MyDB\StoredProcedures

Then just run the same script method from above.
So you can see that scripting objects is wicked easy. And now you can backup the schema of a table before you mess with it, or now you can automate the scripting of SPs before you deploy a new version so you can easily rollback.

2. Now since scripting things like we did above will just print the script out to the screen, you'll want to do something more permenant with them. Have you ever tried to write to a txt file from tsql? You know, you either have to mess with xp_cmdshell or go to one of the sp_OA procedures and make those external calls, right? Well PS solves that problem. So let's script out the objects from above and save them to a file.

gci| %{$_.Script()} | out-file c:\MyTables.txt

That's it!!! So now you can save those things to a file and you've got them to use again.

That's not all you can do with files. You can also get the stuff back out of the files pretty easily. Let's say that you have a list of servers you want to connect to and check that the services are running and you want to turn on the ones that aren't. I'm not going to show you the code for all of the operations here, but to use a txt file as a list of servers you have but to call the txt file and automatically cursor through the items. So here I'll just get the list of servers from a file and print them to the screen. Because what you do with them isn't nearly important here as being able to get them easily.

get-content c:\ServerList.txt | %{$_}

So really, I just listed the servers to the screen one at a time.
The % character is an alias for 'for-each'. So for each item in that txt, print it to the screen. Easy huh?

And just for completion, you can easily work with services by calling the get-service cmdlet or the wmi class for remote boxes.

3. Getting info for objects has never been easier. Let's say you want to get all of the sizes for all of the tables in the DB and do something based on the size. While we're not going to do the entire script, I'll show you how to get the size for all the tables.

First, switch to the tables node.

cd sql:\sqlserver\servername\default\databases\MyDB\Tables

Then run this code:

gci | %{$_.DataSpaceUsed}

That'll just print them out to the screen, or you could pipe them to a txt file like above, or you could continue working with them in the pipeline and manipulate certain tables based off of whatever you like.


There are so many more things you can do with it to make your DBA job so much easier that it would make your head spin. I don't know how we ever got along without it.

Ok, so before I go I'm gonna do just one more because I do this one a lot.
I'm not going to give the code right now, but I'll tell you about it.

Let's say you've got a new employee and you need to add his acct to a Windows group on 30 boxes, and you need to add him to SQL groups and give him access as well. So He's got to be able to TS into these 30 boxes and have certain rights in all DBs on these servers.

With PS I can create a list of servers and either keep them in a table, or put them in a txt file and iterate through them and add the user both to Windows groups, and to SQL groups in a single script. So when's the last time you were able to do that without logging into all the servers individually yourself? And it's really not that hard.

So PS is well worth your time getting to know. If you need help getting started with resources, then ping me and I'll be glad to hook you up.

I've got some really good stuff on PS on MidnightDBA so go out there and check them out.

Here's a overview vid I did back in 2008:
http://midnightdba.itbookworm.com/VidPages/PowerShellOverview/PowerShellOverview.aspx

And you can see most of my other PS vids on the powershell tab of this page:
http://midnightdba.itbookworm.com/Admin.aspx

Good Luck.