SQL script batch execution using sqlcmd in PowerShell

There is often a mismatch between needs of the development team (multiple discreet T-SQL files for separate concerns) and the release team (the requirement  for one step automated deployment) . The script bridges the requirement by using sqlcmd.exe to run a batch of SQL scripts.

A text file is used listing all sql files that need to run in a particular order to avoid errors which may occur if there are dependencies between the scripts. Instead of using a text file a number can be prefixed to the scripts based on the order they need to run.

The script expects two parameters –

  • Path of folder containing the set of T-SQL files (and the manifest file, see below)
  • Connection string

The script can be downloaded from technet gallery.

## Provide the path name of the SQL scripts folder and connnection string
##.\SQLBatchProcessing.ps1 -SQLScriptsFolderPath "C:\Sql Batch Processing\SQLScripts" -ConnectionString "DEV-DB-01"
[Parameter(Mandatory=$true)][String]$ConnectionString ,
Set-ExecutionPolicy -ExecutionPolicy:Bypass -Force -Confirm:$false -Scope CurrentUser
#check whether the SQL Script Path exists
$SQLScriptsPath = Resolve-Path $SQLScriptsFolderPath -ErrorAction Stop
#a manifest file will exisit in the SQL scripts folder detailing the order the scripts need to run.
$SQLScriptsManifestPath = $SQLScriptsFolderPath + "\Manifest.txt"
#Find out whether the manifest file exists in the the SQL Scripts folder
$SQLScriptsManifestPath = Resolve-Path $SQLScriptsManifestPath -ErrorAction Stop

#if manifest file found iterate through each line , validate if corresponding SQL script exists in file before running each of them
Get-Content $SQLScriptsManifestPath | ForEach-Object {
$SQLScriptsPath = $SQLScriptsFolderPath + "\" + $_.ToString()
Resolve-Path $SQLScriptsPath -ErrorAction Stop
$SQLScriptsLogPath = $SQLScriptsFolderPath + "\" + "SQLLog.txt"
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value "Started processing at [$([DateTime]::Now)]."
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value ""
Get-Content $SQLScriptsManifestPath | ForEach-Object {
$SQLScriptsPath = $SQLScriptsFolderPath + "\" + $_.ToString()
$text = "Running script " + $_.ToString();
Add-Content -Path $SQLScriptsLogPath -Value $text
sqlcmd -S "DEV-DB-01" -i $SQLScriptsPath | Out-File -Append -filepath $SQLScriptsLogPath
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value "End processing at [$([DateTime]::Now)]."
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value ""


Create DevTest Labs in Azure

Azure DevTest Labs is available in UK South and UK West as from December 2016, in addition to the other 21 regions it has supported.

The steps to create the DevTest lab are

  • Login to Azure portal as administrator
  • Click the green + New menu


  • Type DevTest Labs into the search box
  • Select DevTestLabs from the results page
  • Click on Create from the Description page.

The advantages using DevTest Labs as mentioned from the Description page are

DevTest Labs helps developers and testers to quickly create virtual machines in Azure to deploy and test their applications. You can easily provision Windows and Linux machines using reusable templates while minimizing waste and controlling cost.

  • Quickly provision development and test virtual machines
  • Minimize waste with quotas and policies
  • Set automated shutdowns to minimize costs
  • Create a VM in a few clicks with reusable templates
  • Get going quickly using VMs from pre-created pools
  • Build Windows and Linux virtual machines


  • Enter the lab name, select the subscription, select location North Europe, tick the Pin to Dashboard tick box and alternatively update the Auto-shutdown schedule.


  • Click on Create.
  • The dashboard is displayed with a new tile showing that the DevTest Lab is being deployed.deployingdevtest-labs_inprogress
  • The DevTest Lab page is displayed once deployment of the DevTest Lab is completed.



Instead of using the Portal, PowerShell can be used to create Azure DevTest Lab. The GitHub repository https://github.com/Azure/azure-devtestlab/tree/master/Samples/ProvisionDemoLab provides an example how it can be achieved.

The repository has a readme file, a deployment template with a corresponding parameters file and a PowerShell script to execute the deployment.

The Readme file provides a description of the resources created.

About the resources created in the Demo Lab:

The ARM template creates a demo lab with the following things:

* It sets up all the policies and a private artifact repo.

* It creates 3 custom VM images/templates.

* It creates 4 VMs, and 3 of them are created with the new custom VM images/templates.

To run the PowerShell script the subscriptionId is required. This can be obtained from the cmdlet Login-AzureRmAccount.


The PowerShell is run as below

.\ProvisionDemoLab.ps1 -SubscriptionId 41111111-1111-1111-1111-111111111111 
-ResourceGroupLocation northeurope -ResourceGroupName RTestLab


The script produces the following results.


From the portal , the result shows the 4 vms.provisiondemolab_portal

The repositories have been created as well.provisiondemolab_repositories

Custom images of the running machines have been created as well.


There are artifacts ready to be used though none are applied yet to the virtual machines.


You can create your own templates/parameters files in the Portal by creating a new resource and exporting  instead of executing the configuration in the GitHub repository.

Create Dev/Test SharePoint 2013 environment in Azure

Azure has a trial image to build either SharePoint 2013 HA farm or SharePoint 2013 Non-HA farm.

When trying to create SharePoint 2013 Non-HA farm, I was stuck at step “Choose storage account type” with the message “Loading pricing…”.


Following SharePoint Server 2016 dev/test environment in Azure, I managed to created a SharePoint 2013 environment in Azure running PowerShell commands.

There are three major phases to setting up this dev/test environment:

  1. Set up the virtual network and domain controller (ad2013VM).I followed all steps described in Phase 1: Deploy the virtual network and a domain controller to set up the virtual network and domain controller
  2. Configure the SQL Server computer (sql2012VM).I followed all steps from Phase 2: Add and configure a SQL Server 2014 virtual machine to create the SQL server computer with few changes to the PowerShell script to create a SQL2012R2 machine.
  3. Configure the SharePoint server (sp2013VM).                                                                               I followed all steps from Phase 3: Add and configure a SharePoint Server 2016 virtual machine with few changes to the script to create a SharePoint 2013 virtual machine.

Configure the SQL Server computer (sql2012VM).

I needed to get the name of SQL 2012 SP2 Azure image offer. I can list all SQL Azure image offers using the cmdlet Get-AzureRMImageOffer.

Get-AzureRmVMImageOffer -Location "westeurope" 
-PublisherName "MicrosoftSQlServer"


The following SQL Image Offers are available


I was interested in SQL 2012 SP2 Standard version. Fortunately the Azure Image Offer Names are intuitive, e.g. Name SQL2012SP2-WS2012R2 means windows server 2012 R2 virtual machine with SQL Server 2012 SP2 installed.

I also needed the SKU value of the SQL 2012 SP2 using the cmdlet Get-AzureRmVMImageSKU

 Get-AzureRmVMImageSKU -Location "westeurope" -PublisherName "MicrosoftSQlServer" 
-Offer SQL2012SP2-WS2012R2|format-table Skus

The following SKUs for SQL2012SP2-WS2012R2 are available


The changes from the original script are on the following lines

  • line 21: “sql2012VM” stored in variable $vmName
  • line 23: $vnet=Get-AzureRMVirtualNetwork -Name “SP2013Vnet” -ResourceGroupName $rgName
  • line 40 : $vm=Set-AzureRMVMSourceImage -VM $vm -PublisherName MicrosoftSQLServer -Offer SQL2012SP2-WS2012R2 -Skus Standard -Version “latest”

Configure the SharePoint server (sp2013VM).

Similarly to creating the SQL virtual machine, I needed the Azure Image Offer Name for SharePoint 2013.

The available SharePoint Azure Image offers for Microsoft SharePoint can be retrieved using the cmdlet below.

Get-AzureRmVMImageOffer -Location "westeurope" 
-PublisherName "MicrosoftSharePoint"

Only one result “MicrosoftSharePointServer” is returned.

To get the available SKUs for “MicrosoftSharePointServer”, the cmdlet below can be run.

 Get-AzureRmVMImageSKU -Location "westeurope" -PublisherName "MicrosoftSharePointServer" 
|format-table Skus


Two results are returned : “2013” and “2016”. I am interested in the “2013” value which refers to the Microsoft SharePoint Server 2013 version.

The changes from the original script are on the following lines

  • line 18: $vmName=“sp2013VM”
  • line 26:$vnet=Get-AzureRMVirtualNetwork -Name “SP2013Vnet” -ResourceGroupName $rgName
  • line 34: $skuName=“2013”

The end result of the PowerShell scripts is a resource group with the virtual machines (adVm, sp2013Vm and sql2012VM), network interfaces, availability sets, storage account and public IP addresses to enable SharePoint 2013 to run in Azure VMs.



Import Configuration Data to CRM using Microsoft.Xrm.Data.PowerShell

CRM Dynamics data can be exported using the data migration tool by first generating the data schema file and using the later to export selected entities and fields. The same data migration tool can be used to import data into other environments. However it is still a manual process.

I have written a script using Microsoft.Xrm.Data.PowerShell module in PowerShell  to automate the data import. The zip generated by the export needs to be unzipped so that the path of the files can be passed to the method called to import the data.unzippedconfigfile

The script can be downloaded from TechNet gallery. Please note that the script has been tested with limited data so might require changes depending on the data being imported.

Call the method Import-ConfigData as in the snippet below.

 Add-type -Path ".\Assemblies\Microsoft.Xrm.Tooling.CrmConnectControl.dll"
 Add-Type -Path ".\Assemblies\Microsoft.Xrm.Tooling.Connector.dll"

import-module ".\Microsoft.Xrm.Data.PowerShell\Microsof t.Xrm.Data.PowerShell.psm1"

$crmOrg = New-Object `         
   -TypeName Microsoft.Xrm.Tooling.Connector.CrmServiceClient `         
   -ArgumentList ([System.Net.CredentialCache]::DefaultNetworkCredentials),             ([Microsoft.Xrm.Tooling.Connector.AuthenticationType]::AD) 
,            $serverName 
,            $serverPort 
,             $organizationName 
,             $False 
,            $False 
,           ([Microsoft.Xrm.Sdk.Discovery.OrganizationDetail]$null$dataFilePath =  "\PkgFolder\Configuration Data\data.xml" 
$dataSchemaFilePath = "\PkgFolder\Configuration Data\data_schema.xml" 
Write-Output "Begin import of configuration data..." 
Import-ConfigData -dataFilePath $dataFilePath 
-dataSchemaFilePath $dataSchemaFilePath -crmOrg $crmOrg 
Write-Output "End import of configuration data..."

First initialise the crm connection object of type Microsoft.Xrm.Tooling.Connector.CrmServiceClient stored in the variable $crmOrg above.

Pass the following parameters to the method.

datafilePath: Path of the file data.xml

crmOrg  : crm connection object

dataschemafilepath: Path of the file dataschema.xml



Create Virtual Machine(VM) in Azure throws “Long running operation failed with status ‘Failed’.” through PowerShell

I was following the article SharePoint Server 2016 dev/test environment in Azure to create a SharePoint Server 2016 environment in Azure.

I reached the step to create the Virtual Machine that will be used as the domain controller using the cmdlet below

New-AzureRMVM -ResourceGroupName $rgName -Location $locName -VM $vm

After waiting for several minutes I decided to cancel the operation. When I retried the same cmdlet, I got the following error.

New-AzureRMVM -ResourceGroupName $rgName -Location $locName -VM $vm -Verbose
VERBOSE: Performing the operation "New" on target "adVM".
New-AzureRMVM : Long running operation failed with status 'Failed'.
ErrorCode: VMAgentStatusCommunicationError
ErrorMessage: VM 'adVM' has not reported status for VM agent or extensions. 
Please verify the VM has a running VM agent, and can establish 
outbound connections to Azure storage.
StartTime: 18/11/2016 15:42:46
EndTime: 18/11/2016 15:42:46
OperationID: f1ecb302-9da9-4a76-9b0c-463b5e89c41c
Status: Failed
At line:1 char:1
+ New-AzureRMVM -ResourceGroupName $rgName -Location $locName -VM $vm -Verbose
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 + CategoryInfo : CloseError: (:) [New-AzureRmVM], ComputeCloudException
 + FullyQualifiedErrorId : Microsoft.Azure.Commands.Compute.NewAzureVMCommand

I spent a while trying to understand what the error message meant. In that scenerio the error message was thrown because the VM requested was created successfully even though I interrupted the operation before. The error thrown was to prevent creating another VM with the same details.

When I created the second VM, I waited long enough till the success message was displayed.


Create Azure Virtual Network throws Error “Subscription is not registered with NRP”

I was following the article SharePoint Server 2016 dev/test environment in Azure to create a SharePoint Server 2016 environment in Azure.

I reached the step to create the SP2016Vnet Azure Virtual Network that will host the SP2016Subnet subnet.

I tried  the cmdlets below

$spSubnet=New-AzureRMVirtualNetworkSubnetConfig -Name SP2016Subnet 

 New-AzureRMVirtualNetwork -Name SP2016Vnet -ResourceGroupName $rgName 
-Location $locName -AddressPrefix -Subnet $spSubnet 

Unfortunately I got the error message

WARNING: The output object type of this cmdlet will be modified in a future release.
New-AzureRMVirtualNetwork : Subscription <Guid> is not registered with NRP.
StatusCode: 409
ReasonPhrase: Conflict
OperationID : '0937045e-3d71-411f-ba11-785e5fcff586'
At line:1 char:1
+ New-AzureRMVirtualNetwork -Name SP2016Vnet -ResourceGroupName $rgName -Location ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 + CategoryInfo : CloseError: (:) [New-AzureRmVirtualNetwork], NetworkCloudException
 + FullyQualifiedErrorId : Microsoft.Azure.Commands.Network.NewAzureVirtualNetworkCommand


After spending a while googling through solutions , I gave up and tried to modify the script until I get it working.

The fix was to enclosed the parameter “Name” value between double quotes when calling method New-AzureRMVirtualNetworkSubnetConfig.

-Name "SP2016Subnet" -AddressPrefix

New-AzureRMVirtualNetwork -Name SP2016Vnet 
-ResourceGroupName $rgName -Location $locName 
-AddressPrefix -Subnet $spSubnet -DNSServer

The cmdlets work after the fix.



Instantiate CRMServiceClient using current user’s login

The Get-CrmConnection method can be used to  return connection to a CRM instance. The syntax to call the method is

Parameter Set: OnLine
Get-CrmConnection [-OnLineType] <OnlineType> [[-Credential] <PSCredential> ] 
[-DeploymentRegion] <String> [[-ProfileName] <String> ] -OrganizationName <String> [ <CommonParameters>]

Parameter Set: OnPrem
Get-CrmConnection [-ServerUrl] <Uri> [[-Credential] <PSCredential> ] 
[-OrganizationName] <String> [[-HomeRealmUrl] <Uri> ] [[-ProfileName] <String> ] 
[ <CommonParameters>]

Parameter Set: UIOnly
Get-CrmConnection [[-InteractiveMode]] [ <CommonParameters>]

I wanted to get the crm connection with the current user’s credentials without any prompts. The first and second options required the object PSCredential which can’t be created using logged current user’s credentials. The third option with the switch InteractiveMode  displays a dialog box prompting to enter connection details. All three options were not appropriate for the requirement.

The method returns the object Microsoft.Xrm.Tooling.CrmServiceClient. From the msdn article, it can be constructed using the NetworkCredential object.

The constructor’s definition in C#

public CrmServiceClient(
	NetworkCredential credential,
	AuthenticationType authType,
	string hostName,
	string port,
	string orgName,
	bool useUniqueInstance = false,
	bool useSsl = false,
	OrganizationDetail orgDetail = null


In PowerShell, the current user’s credentials can be retrieved using [System.Net.CredentialCache]::DefaultNetworkCredentials.

There is no way the [System.Net.CredentialCache]::DefaultNetworkCredentials can be converted to the PSCredential object.

The below syntax can be used to create the Microsoft.Xrm.Tooling.Connector.CrmServiceClient using logged in user’s credentials. Replace the variables $serverName, $serverPort, $organizationName with the respective values.

 $crmConnection = New-Object `
 -TypeName Microsoft.Xrm.Tooling.Connector.CrmServiceClient `
 -ArgumentList ([System.Net.CredentialCache]::DefaultNetworkCredentials),