Global Excel Automation PowerShell Library : DPM, Hyper-V and SQL goodies

Today I’ve committed several changes and additions to the GEMAutomation project on Codeplex. Here’s an overview of what’s new:

Added the following:


This is a small script to rename ps1 file that contains functions to psm1 files (PowerShell Module)

This script goes through PowerShell scripts and module to gather function usage. Using this, you can build a dependency graph for your PowerShell code. I’ve took the resulting data and used NodeXL to display the dependencies in a graph format using Excel. For larger libraries, you could import the data in Neo4j to make it more queryable


Right now, this only contains a function to restore the latest recovery point of a SQL Server database to any SQL Server instance connected to the Data Protection Manager server.


This goes through all the VHDs/VHDXs in an Hyper-V cluster to discover their hierarchy when applicable. For instance, if you have VMs built from a template disk (parent) using differencing disks, this will allow you to easily identify which parent disks are in use. The resulting list is currently stored in SharePoint.

This contains a few methods:
– Get-HyperVClusterNodes (Gets information on all the nodes part of an Hyper-V cluster)
– Get-RemoteVMClusterResource (Gets the information of a VM resource remotely)
– Get-ClusterSharedVolumes (Gets information on all the CSV mounted in an Hyper-V cluster)
– Select-ClusterSharedVolume (This is not used due to an unknown issue, ask me for details!)

This is used by the Get-VHDHierarchy.ps1 script to gather the VHDX hierarchy.

– libVMHardDisk.psm1
This contains the following method:
– Unmount-VMHardDiskDrive (Unmounts a disk that’s mounted in a path)
– Add-VMHardDiskDriveAccessPath (Adds a mount point to a VM disk)
– Mount-VMHardDiskDrive (Prepare a VM hard disk by putting it online, clearing the read-only attribute and assign the proper volume label. If it’s a new disk,
it will be initialized, partitioned and formated.)
– New-RemoteDifferencingVHD (Remotely creates a new differencing disk.)
– New-RemoteVHD (Remotely creates a VHD)
– Add-RemoteVMHardDiskDrive (Remotely add a VHD to a VM.)
– Create-RemoteVMHardDiskDrive (Remotely create a VHD and attach it to a VM.)
– Get-RemoteVMHardDiskDrive (Get the VMHardDiskDrive information for particular disk for a VM.)
– Remove-RemoteVMHardDiskDrive (Remotely remove a VM hard disk drive.)
– Create-ParentDisk (Generic function to create new parent disk, mainly used right now to create SQL Server data parent disks using the Create-SQLDataParent.ps1 script)


This script performs the following:
– Create a new VHDX and mounts it in a VM
– Restore databases from Data Protection Manager based on a configurable list
– Prepare the databases restored by defragmenting the indices and dropping the users present
– Notify people by email once the process is completed

We use this internally to create a master copy of our production systems. We then create differencing disks in Hyper-V which are environment specific and attach them to the desired development or quality assurance SQL Server instance. Once the parent disk is created, we can very quickly refresh an environment with hundreds of GB of data in a few minutes and we reduce the storage required for all our Dev/QA environments. Each environment is then free to use the delta disk as they wish by upgrading the database schema, adding/deleting/updating the data without affecting the parent disk as all changes are written in the differencing disk.

– Get-SQLDataParentDatabases (Gets the list of the databases that are part of a certain parent definition)
– Get-DatabaseRefreshScripts (Gets the list of scripts that need to be run after the database clone has been attached in an environment)
– Get-ParentVersions (Gets the available version of a certain parent type)
– Get-ParentNames (Gets the list of the available parents definition)

Creates a SQL Server data differencing disk based on a specific parent disk.

Removes a SQL Server data differencing disk from the VM by first detaching the databases contained in the disk.

Removes a parent disk from the VM. This is usually ran after the Create-SQLDataParent.ps1 has completed.

If you have any questions regarding those scripts, feel free to comment here or start a discussion on the Codeplex site.

You can download the new release here:
GEM Automation Latest Release


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s