SQL Database Report Revised

Last year I wrote an article that explained how to use the SQLSERVER PSDrive to create an HTML report highlighting some server and database information. If you want a refresher you can find that article here. In short, you can install the SQL Server PowerShell module on your client desktop and use it to manage remote servers.
During a PowerShell class I was teaching this week, I included some content on the SQL cmdlets and had an opportunity to revisit the original script. Even though the original version was intended to work with remote servers, I had a few bugs that prevented that from happening. I also decided to include some additional information as well as incorporate more conditional formatting. For example, depending on how much free space is left, the report might format the value in yellow, to indicate a warning or in red to indicate something more critical.

In the SQLSERVER PSdrive, the Databases directory doesn’t show any system databasese by default unless you use -Force.
sqldatabases
So I updated the script to allow you to include those databases if you want.

Here is the revised script:

My script includes a graphic file to make it pretty. You can download my graphic file db.png. Put the graphic in the same directory as the script. Or modify the script to use your own graphics. You can then run a command like this to generate the HTML report.

This script lacks a provision for alternate credentials and assumes the account you are running under has the necessary SQL permissions. When finished you can get a report like this.

I hope you’ll let me know what you think and if you find this useful.

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.

Creating Styling HTML Reports with PowerShell

Last month I did an updated version of my presentation on creating styling HTML reports in Windows PowerShell. This presentation was for the PowerShell virtual chapter of SQL PASS. As such, I came up with some SQL related demonstrations and fine tuned some demos from earlier presentations.

You can download a zip file with a PDF of my slides as well as my demo scripts.