Friday Fun: A Random PowerShell Console

crayonsThis week I thought we’d have a little fun with the PowerShell console and maybe pick up a few scripting techniques along the way. Today I have a function that changes the foreground and background colors of your PowerShell console to random values. But because you might want to go back to your original settings without completing restarting PowerShell the function allows you to reset to your original values. Oh, and it also supports -Whatif. Here’s what I came up with.

The function will not work properly in the PowerShell ISE so I’ve included some code at the beginning to see if it is running in the ISE. If so, the command displays a warning and bails out. This is a scenario where using Return is completely acceptable as I want to return out of the pipeline without doing anything. I could have used a command like this: Return “This command only works in the PowerShell console.” but that would have written a string object to the pipeline and I don’t want anything to go to the pipeline. Plus, I prefer to use Write-Warning for messages like this.

When you run the command, it tests for the existence of some variables, $savedfg and $savedbg, that are defined in the global scope. You’ll notice the use of the global: prefix. If they are not defined, then the assumption is that this is the first time the command has been run and the variables will be defined with the current values of the $host.ui.rawui.foregroundcolor and $host.ui.rawui.backgroundcolor values. Later, if you use -Reset, the command will use these values to restore your original settings.

Otherwise, the command gets a random color for the System.ConsoleColor enumeration for the background and then another random color for the foreground, that is different than the randomly selected background color.

When you look at the code, you will see that I am specifying in the cmdletbinding attribute that the function supports ShouldProcess. That could also be written [cmdletbinding(SupportsShouldProcess=$True)] but that seems redundant to me. Anyway, the command to make the change, $host.ui.rawui.backgroundcolor= $bg, by itself doesn’t know anything about ShouldProcess and -WhatIf. But I can add my own code using an If statement.

The value for the ShouldProcess() method is the text you see as part of the “…performing operation on target…” message. The text is the target.

All I’ve done is define a variable, $msg, to make the line of code easier to read. As you can see, it is not that difficult to add your own support for -WhatIf. And now, if you get a little bored, mix it up for a fresh perspective. I’ve even included an alias, src, in case you have to type in a console where you can’t see what you’re typing.

Have a terrific weekend.

So you need to write a PowerShell script

lightbulb-idea So…you have decided to write a PowerShell script or have at least identified a need. What do you do first? If you say “Google or Bing”, I’d say you are wrong. In my opinion, when you are developing a PowerShell script, searching for an existing script is not the first step. Sure, you will likely find something, but….

There are many online sources of PowerShell scripts and code samples. However, from my experience the quality is all over the board. Sure, you might find a great example. But unless you have a great deal of PowerShell experience, how will you judge? Sadly, many script and code samples I see don’t follow community accepted best practices, don’t follow the PowerShell paradigm or are simply bad scripts. There is also absolutely no guarantee that the script or code sample you download will work correctly (and safely) in your environment.

Personally, I would recommend that you open your script editor and start laying out a series of comments about what it is that you need to accomplish. If you are using the ISE you might even use regions to outline your script. This task helps your organize your work, and when you are finished, the script is documented? All you have to do is write the code to fulfill the comments. Yes, that might be difficult and maybe even a little time consuming at first, but that’s the point. You will be learning much more than by simply copy and pasting something of dubious quality you found online. Even more importantly, you will be developing something that you know will work in your environment.

Get stuck? Then sure, look online to find examples of how someone used a particular cmdlet or function. But try to find several examples and “average” them out. Perhaps even better would be to post in the forums at and ask for specific help on a sticky problem. You’ll likely get several responses. And knowing the quality of the average forum member, I’d feel very comfortable with their responses.

Over time, as you gain more PowerShell experience, you will be better able to assess the quality of online PowerShell scripts and samples. I still think you should develop on your own from scratch, but I also think you’ll find the process goes much faster.

SQL Database Reports with PowerShell

talkbubbleLast year I wrote an article for SQL Server Pro called PowerShell in SQL Server. In the article I provided an introduction to using PowerShell and the SQL Server module to do perform some typical management tasks. I think this type of information is especially important for those of you who have to manage a SQL Server but perhaps aren’t really a DBA. The other day I received an email about a way to list all databases and their associated files. While I go over that somewhat in the original article, I thought I’d revisit and expand a bit on the topic here.

In order for this to work you need the SQLPS module. Or you should be able to use PowerShell remoting with Invoke-Command. On my Windows 8.1 desktop I have the free SQL 2012 Express edition, primarily so that I can have access to the PowerShell module and specifically the Invoke-SQLCMD cmdlet which allows me to run a T-SQL command from my desktop to any SQL server. But that’s not on the plate today.

Instead we’re going to take advantage of the SQL Server provider. When you import the SQLPS module, you will get a new PSDrive called SQLSERVER:.


I can now navigate my SQL server instance like a file system.

I changed to the SQL “folder” then to the server (jh-win81-ent-01 is my local host) and then to the named instance, in this case Default and finally the Databases container.

Now that I know where to look I display just the information I want.

I discovered these properties by using Get-Member and piping to Select *.

Once I know the properties, it is pretty easy to display the required information. The challenge was to display databases and their files. The file information is buried a little bit but not that really difficult to access.


By the way, the sizes are in MB. The results are objects like anything else in PowerShell so you could sort, filter, export, convert or whatever you need to do with this information. I think I’ll create an HTML report.


Assuming the current credentials have the right permissions on a remote SQL Server, you can also do this remotely. Simply change the machine name in the path. Here’s my modified HTML report code.

If you run into performance or timing issues, simply use remoting.

Remoting is a great option if you need to use alternate credentials or if you want to process several SQL Servers at once.

PowerShell works the same whether you are looking at a process, event log or SQL Server database. Once you master the fundamentals there’s no end to what you can accomplish.

Advice, solutions, tips and more for the lonely Windows administrator with too much to do and not enough time.

%d bloggers like this: