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.

VMDK to VHDX PDQ

I have a very old VMware ESXi server that has outlived its useful life. The hardware is at least 5 years old and my VMware license has expired. I can still bring up the server and see the virtual machines, but that’s about it. I still keep the box so I can run the PowerCLI cmdlets, at least in a limited fashion. However, there are a few virtual machines that I need to get at so I can move some applications and data to another Hyper-V virtual machine. Since I can’t get the VMware virtual machine running, I can at least convert the disk to a VHDX and bring it up in a new Hyper-V virtual machine.

I already have PowerCLI 6.0 and the Hyper-V cmdlets installed on my computer. My initial thought was to use the free Microsoft Virtual Machine Converter which you can download. But I ran into a number of issues using the GUI, primarily because not everything is in the same domain. But that doesn’t really matter because I didn’t really want to migrate the complete virtual machine, I just needed to bring up the old VM temporarily so I could migrate things off. Fortunately, there are a set of cmdlets that ship with Microsoft’s converter. Here’s what I did.

First, I needed to connect to my ESXi box using the PowerCLI cmdlets.

I need to get the disk files for a given virtual machine. One benefit of PowerCLI is that you can easily browse the datastore files.

I’ll need all of these files. But first I need to be able to access them from the file system. Fortunately, PowerCLI has a handly cmdlet for copying items from a datastore to the file system.

Once the files are copied I can begin the conversion. However, the Microsoft virtual machine converter cmdlets aren’t installed in an expected location so I’ll have to manually import them.

Once imported, I can use ConvertTo-MvmcVirtualHardDisk.

The conversion took about 20 minutes for a 40GB file. The converted file was 30GB for a dynamic hard disk. With the conversion complete, it is pretty easy to fire up a new Hyper-V virtual machine.

You’ll notice that I am using a complete path for the New-VM cmdlet. That’s because in my session I have both PowerCLI and Hyper-V cmdlets and they both have a New-VM cmdlet. Normally I wouldn’t have both running in the same session but since I do, I need to explicitly tell PowerShell which cmdlet to use.

And that’s about it. Once running I uninstalled the VMware Tools, installed the Hyper-V Integration Toolkit and let Windows detect everything else. This would require me re-activating Windows, but I’m hoping to migrate everything I need before that becomes an issue.

 

Friday Fun: A Better PSEdit

In the PowerShell ISE, there is a built-in function called PSEdit. You can use this function to easily load a file in to the ISE directly from the ISE command prompt.

You can also load multiple files, but not as easily as you might like. I find myself wanting to do this:

As you can see isn’t what I’m expecting. I can get PSEdit to open multiple files, but I need to use a command like this:

I finally tired of this so I looked at the code for the PSEdit function.

I am assuming based on what I see that this was written a long time ago. So I decided to update it. Here’s my version:

The major difference is that my version works in the pipeline making it easier, for me at least, to open multiple files at once.

I also added some verbose messages for troubleshooting. This is my common practice when creating new PowerShell tools. You’ll also notice that I replaced the aliases in the original function with complete cmdlet and parameter names.

The last “feature” is my customized ValidateScript attribute. I wanted to verify that any path pointed to a legitimate file. I could have simply used this:

But if the path failed the test, PowerShell displays a long error message that isn’t always helpful. So I added some logic. Validation tests have to return either True or False. When it is false, PowerShell throws the exception. So I wrote my own exception message.

I get a similar error with the original psedit.

So perhaps I haven’t improved on it that much. But I could have written an even longer message and I wanted to demonstrate this technique in case you wanted to use it.

One last word on my version of PSEdit. I didn’t use a standard name, I guess because the original function doesn’t use one. And I’m ok with that. This is one of the situations where the function is a “cheater” command with a simple, alias-like, name. If you want to replace the original PSEdit function, add mine to your ISE profile script and rename it to PSEdit.

Enjoy.

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