I have written powershell scripts in Azure runbooks in Azure Automation. It’s not a new concept. It’s even from back in 2014


I started to use it because there is no SQL Agent in Azure SQL databases. I relied on SQL Agent to perform Ola’s database maintenance scripts. I use the Azure automation with Runbooks now for a long time to build reports from Azure SQL and have them send to people by SMTP.

The problem is that I string concatenate HTML in the powershell script and just put the results in an HTML enabled Email message. It is still a good option… Until a coworker requests an Excel attached to the mail…

Excel in Azure Runbook (Powershell)

I did build the powershell locally first.


When using the Azure Automation ISE add-on for Windows PowerShell ISE it hit me. The cloud probably has no Excel com/interop…

So I found this module to work with Excel in Powershell without Excel on GitHub. It uses Epplus. Which I mentioned in my post from 6 years ago.

But I realised that I could also just use Azure Functions and code in C# and have a time trigger. This enables me to write my beloved C# rather then scripting Powershell. I can also just use the Epplus nuget package.

The Azure functions v2 are now in preview and have .Net Standard support (which is great!)

The Visual Studio dialog can be unclear if you visit it for the first time and have no clue that the schedule uses CRON notation. Maybe they will change it, but now you know.

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn

There are a lot of benefits with managed disks and it is the preferred way to create a new VM. However because of my tight budget, I wanted to move to unmanaged. I did not expect the costs to continue when the VM was stopped. It was because of the managed storage disks(s).

I found this SO answer of Jason Ye - MSFT.

$sas = Grant-AzureRmDiskAccess -ResourceGroupName "[ResourceGroupName]" -DiskName "[ManagedDiskName]" -DurationInSecond 3600 -Access Read  
$destContext = New-AzureStorageContext –StorageAccountName "[StorageAccountName]" -StorageAccountKey "[StorageAccountAccessKey]"
$blobcopy=Start-AzureStorageBlobCopy -AbsoluteUri $sas.AccessSAS -DestContainer "[ContainerName]" -DestContext $destContext -DestBlob "[NameOfVhdFileToBeCreated].vhd"

I had to create containers in the storage account but I had copied both the OS and the Data disk to the blob storage as .vhd file.

I used this powershell script and the template to create the vhd from blob storage. The datadisk can be added later in the web gui.

  1. Login-AzureRMAccount
  2. Get-AzureRmSubscription
  3. Set-AzureRmContext -SubscriptionName "my subscription name here"
  4. $sas = Grant-AzureRmDiskAccess -ResourceGroupName "resourcegroup" -DiskName "manageddiskname" -DurationInSecond 45000 -Access Read 
    $destContext = New-AzureStorageContext –StorageAccountName "storageaccount" -StorageAccountKey "myprivatekey"
    $blobcopy=Start-AzureStorageBlobCopy -AbsoluteUri $sas.AccessSAS -DestContainer "vhd-containers" -DestContext $destContext -DestBlob "givetheunmanageddiskaname.vhd"
  5. Get-AzureStorageBlobCopyState -Container "vhd-containers" -Blob "givetheunmanageddiskaname.vhd" -Context $destContext –WaitForComplete

My mistake was to use the 3600 value for ‘DurationInSecond’ which is just an hour (60 sec, 60 minutes). The 512 gb datadisk could not be copied to blob storage within an hour (or two). Found out that an hour was also insufficient when I found ‘Get-AzureStorageBlobCopyState’.

Because I already had a vnet from my vm with managed disks, I used this template to create a new vm with the os disk from blob storage: https://github.com/Azure/azure-quickstart-templates/tree/master/201-vm-specialized-vhd-existing-vnet

If you do not have a vnet yet, you should use https://github.com/Azure/azure-quickstart-templates/tree/master/101-vm-from-user-image The deploy to azure button is a useful tool!


Once you have a new vm with an unmanaged disk up and running, close it to add the data disk. Once you have done that and have a remote desktop connection, go to disk management and bring the datadisk online again. It took me some time to get my head around the ASM and ARM differences in the powershell tooling. Also because there is now Azure Cli and a cross plat powershell 6.0

The cloud is moving fast, so hop on. Don’t miss out!

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn