As long as I’m on a roll here with server-level ops, I thought I’d go ahead and cover dropping DBs in PS. This should be a fairly short post as there’s not much to this, but it’s worth talking about anyway.

Dropping DBs isn’t any big deal in T-SQL:

DROP DATABASE SeansDB

And the PS method for doing this is of course a 1-liner, but it’s not as easy as T-SQL. Again there’s more than one method for doing it and I’ll show you both of them. Here, whether you connect through sqlps, or through PS proper, you want to be at the Database node. Here’s a screenshot of doing it from sqlps, but again, it really doesn’t matter.

Method 1

dir | %{$_.Drop()

There are a couple things to note here. First of all, this is typical PS, so if you run this cmd as it’s written here, you’ll drop every DB on your server… totally uncool. And 2nd, I’m always talking about how if you see an Alter() method on the get-member list then that usually means it wants you to use it before the changes are pushed to the server itself. Well, this is one of those times that makes me put the ‘usually’ in there because while that’s a good rule of thumb, PS is nice enough to drop objects when you ask it to. So anyway, unless you want to lose everything, I wouldn’t run the code above. I just wanted to have a basis for future syntax.

So all we have to do now is limit our result list to the DB we’re interested in:

dir | ?{$_.Name -eq "SeansDB"} | %{$_.Drop()

It just doesn’t get any easier than that. Now, at this point T-SQL is still ahead, cause even I would still much rather use T-SQL for dropping a single DB. Powershell is going to pull ahead pretty fast when it comes to dropping several DBs, or even a single DB on multiple servers.

Let’s say you’ve got several test DBs, and they all have the word ‘test’ in them somewhere. Since different devs created them, they don’t have a solid naming convention. Hell, some of them may even just have ‘tst’ in them, who knows, right?
At this point it’s just a matter of altering the above script so that it can accommodate your criteria.

dir | ?{$_.Name -match "test" -or $_.Name -match "tst"} | %{$_.Drop()

T-SQL would require you to code a cursor for this, and while the for-each is technically a cursor in PS, it takes next to no coding for us. PS is starting to pull ahead a little now. And by simply changing the where-object criteria, you can easily change this script to do things that are more difficult in T-SQL like dropping DBs that are all owned by a certain login, or were created on a certain date, or are under or over a certain size, or even haven’t been backed up recently. Of course, some of that criteria you’d never use, but it’s all there for you. And again, you can find out what you can use by doing a get-member and anything that comes up as a property is usable.

dir | gm}

Method 2
Now let’s look at a shorter method for dropping a single DB.

(dir SeansDB).Drop()}

That’s pretty easy, and to do multiple DBs, it could look something like this:

(dir | ?{$_.Name -match "test" -or $_.Name -match "tst").Drop()

Now, if you have a DB that exists across multiple boxes then you can drop all of them like this:

$a = "server1", "server2", "server3"
$a | %{
$ServerName = $_; ## Just setting this to make the script easier to read.
cd sqlserver:\sql\$ServerName
(dir SeansDB).Drop()}

And that’s it. Going against multiple boxes is so easy. And the way I’m doing it by setting the $a var to the server list, you can easily populate $a anyway you like and you don’t have to change the rest of the script. So make it the results from a SQL query, or from a txt file, etc. It doesn’t matter.

OK, so that’s it this time. Be careful with this or you’ll find yourself doing a recovery on all your DBs. Man, it would be really easy to be evil with this huh? In less than 60secs you could kill every DB in your company if you wanted to… say if they fired you for no reason and you wanted to get back at them. I’m not saying you should, I’m just saying it’s possible.
And after something like that I’m compelled to say (again) that I’m not responsible for your (mis)use of any of these methods.

Ok, I was on this blog today and it showed a cool Powershell method for checking which cluster node is active.  And while there’s nothing wrong with the script, it does go to show the thing I dislike the most about how many DBAs work with Powershell in SQL Server.  Now, I can’t stress enough that I’m not picking on this guy, and I’m not saying he’s wrong.  I’m just saying that I prefer to rely on the built-in methods of the provider because it’s just simpler.  Here’s what I mean.

I’m going to borrow his code for a moment just to show you an example.  But I encourage you to visit his blog yourself as he’s got some cool stuff out there.  Anyway, here’s his method for doing this in PS:

# Set cluster name
$cluster_name = "ClusterName";
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null;
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $cluster_name;
# Get server properties
$properties = $srv.Properties
$owner_node = $properties.Item("ComputerNamePhysicalNetBIOS").Value;
$is_clustered = $properties.Item("IsClustered").Value
if($is_clustered)
{
	Write-Host "The current active node of $cluster_name is $owner_node.";
}
else
{
	Write-Host "$cluster_name is not a clustered instance of SQL Server.";
}

Overall, there’s nothing wrong with that except it’s just too long.  See, too many DBAs forget what Powershell is all about so they’re constantly re-inventing the wheel.  I like to use the kind of wheels I’m given whenever possible.

Here’s how I would tackle this same task (there are actually 2 ways):

Method 1

>$a = dir

>$a.ComputerNamePhysicalNetBIOS

Method 2

>(dir).ComputerNamePhysicalNetBIOS

And that’s it.  Why would you want to type all that other stuff when you can so easily type just a few characters… 33 to be exact. 

All you have to do is make sure you’re in the server node of the provider tree. 

So if you connect through sqlps, then you’ll right-click on the server itself and that’ll take you to the Default instance.  Then you just need to go one level up by typing ‘cd ..’  From there, just type one of the lines above and you’re golden.

Oh yeah, his script also told you whether the box was even clustered to begin with.  We can handle that with another line.

>$a.IsClustered

That returns ‘True’ so if you want to make it pretty for yourself then just a quick change can make that happen.

>IF ($a.IsClustered) {“It’s clustered alright.”} ELSE {“No it isn’t.”}

And strictly speaking you don’t need the ELSE in there.

Now, using both methods you can easily cycle through a ton of boxes so there are no worries there.  

I’m gonna get on my soapbox for a minute and say that this is the bulk of the PS I see being taught and to me it shows a lack of fundamental understanding of what PS is supposed to do for us.  I just prefer to use what the provider gives me.  And it really matters too.  If you find yourself somewhere without your scripts, which happens to me all the time when I’m working from a user’s box, or at another shop, then you’ve got to remember all that SMO provider info.  My method is not only much easier to remember, it’s much easier to investigate because you can easily do a GM against $a to see all the properties it has.  But if you go even just a couple weeks without typing in that code to load the SMO provider, you can forget nuances and you’ll find yourself looking something up.  And that’s not efficient DBA work.  PS is supposed to make our jobs easier, not complicate them.  PS isn’t just a replacement for VBScript.  It’s an entirely new way of thinking.  And there are tons of guys out there teaching PS who haven’t switched to the PS way of thinking.  To me this is the exact same thing as those guys who use those really complicated cursor methods for finding log space usage when they could just type DBCC sqlperf(logspace).  Can you do it the other way, sure, but why would you when the SQL team has given you such an easy method?  This is why most of the time whenever I see a PS blog somewhere, I typically have to translate it into real PS.

So guys, let’s get rid of all that needless SMO code and bring PS back to what it’s supposed to be… simple one-liners that make our job easier.

I also did a video last week about changing server-level properties that talks you through the same methods as well.  Take a look.

http://midnightdba.itbookworm.com/VidPages/PowershellServerProps/PowershellServerProps.aspx