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 PowerShell.org and ask for specific help on a sticky problem. You’ll likely get several responses. And knowing the quality of the average PowerShell.org 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:.

sqlpsdrive01

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.
sqlpsdrive02

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 *.

sqlpsdrive03
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.

sqlpsdrive04

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.

sqldbreport

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.

PowerShell MVP Again

MVP-Logo-small I am happy to say that I have been renewed for my 8th year as a PowerShell MVP. I am honored to be in such great company with other MVPs. I am also extremely grateful to members of the PowerShell community and I suppose beyond that, who find my work useful or at least worth a few minutes of their time. As with renewal, I feel empowered and challenged to bring you even more PowerShell related content, whether it be blog posts, Prof. PowerShell columns, online webinars, training courses or conference sessions. Thank you for your trust in me. Now I guess I’d better get back to work.

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

%d bloggers like this: