PowerShell Basics

May 12, 2009 at 6:57 am (PowerShell, SQLServerPedia Syndication) (, , )


I’m just learning how to use PowerShell. I’ve been trying to spend time with it over the last year, ever since I saw Allen White‘s presentations at the PASS Summit last year. I just haven’t had the time. But recently, my company has been performing a multi-phased data center move. Because of it, I’ve needed to run tests & updates, disable jobs, all kinds of things on different sets of servers on different days. To paraphrase the old Superman cartoons “This looks like a job <voice gets deeper> for PowerShell.”

I’m not even remotely qualified to begin teaching anyone PowerShell. I had problems recently getting an IF conditional statement to work correctly. However, in the interest of sharing, in case you’re thinking about picking it up (do) or you’re actively working on trying to apply it to your SQL Server maintenance routines, let me give you a small piece of advice. There are millions of things you can do with this. Maybe more. But one of the most common that I see is simply getting a list of servers and then looping through the list performing an action on the server. You can do this using some of the built in sql server gadgets, or, because PowerShell is a .NET language, you can take advantage of constructs such as SMO (SQL Server Management Objects). I’ve done both, depending on whether I was trying to manipulate data, running scripts through the PS equivalent of sqlcmd, or manipulating the servers & objects, SMO. This really simple script can help you get going (at least, I’m using it for tons of stuff):

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
foreach ($server in Get-Content "c:\Scripts\ServerList.txt")
{
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$server"
if($srv.Information.VersionMajor -eq "10")
{
#Perform a command on SQL Server 2008
}
elseif($srv.Information.VersionMajor -eq "9")
{
#Perform a command on SQL Server 2005
}
else
{
#Perform a command on SQL Server 2000
}
}

Assuming I typed all that correctly (a little tighter syntax checking would be nice), this will cycle through all the servers included in the text file ServerList.txt.

This isn’t rocket science. You could have figured this out for yourself. Hopefully this saves you five or ten minutes. As I figure out more interesting things (assuming I do), I’ll post them up here.

2 Comments

  1. New Gadgets | PowerShell Basics said,

    […] Original post by Home of the Scary DBA […]

  2. Log Buffer #146: a Carnival of the Vanities for DBAs | Pythian Group Blog said,

    […] Scary DBA offers some PowerShell basics. Others are not so keen—Bill G.’s thoughts on SQL Server include, Why am I being forced […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: