Search This Blog

Monday, May 07, 2018

Powershell: Format Fixed Width String (-f format operator)

Had to build a table in a text format of a predetermined width. What I found is that the –f operator can assist with building this

I had a variable that I stored the text output in, and then appened the results to it.

Add table header to string variable

$StringOutput += "{0,-80}{1,10}{2,25}" -f 'Filename','Size (KB)','Date';

Then loop through results

foreach (item in list)


$sqlStringOutput += "{0,-80}" -f $item.Name + "{0,10}" -f ("{0:n2}" -f ($item.Length/1kb)) + "{0,25}" -f ("{0:yyyy-MM-dd HH:mm:ss}" -f ([datetime]::ParseExact($item.LastWriteTime,'MM/dd/yyyy HH:mm:ss',$null))) + $htmlbr;



Filename                                                                         Size (KB)                     Date

filename1.bak                                                                         0.50      2018-04-22 22:37:40
filename2.bak                                                                         0.50      2018-04-23 22:39:35

So I have used different ways to build the string using formatting.

1. "{0,-80}{1,10}{2,25}"  - this is one format string, the first number 0,1,2 are the indexes of the following strings. the –80,10,25 are how wide the end string is (with padding), the – number means align left, positive align right.

2. I used multiple format strings then concatenate them together, I just found it easier to read (due to nested format strings on the numbers and dates.



Thursday, May 03, 2018

Powershell: reveal hidden/truncated data in output

Things to try

1. $FormatEnumerationLimit = –1

2. Select-Object –ExpandProperty <property>

Output will display everything.


Powershell: List basic path info for all folders recursively

Had the need to create one big list of all folders in a folder tree. I could get this using Get-Childitem but it broke it down into a nicely formatted output, which is not what I needed.

I wanted just one path per line.

I came up with the following. Initially the output was being truncated, so long paths were truncated with …

This truncate can be removed by setting the following variable to –1


Not sure why but $FormatEnumerationLimit didnot work, I have to use the ExpandProperty function of the select-object cmdlet.

Get-ChildItem -path \\server\rootfolderpath -Recurse | ?{ $_.PSIsContainer }| Select -ExpandProperty FullName | Format-table FullName > folders.txt


Wednesday, April 25, 2018

Office 365–Remove Office365 user/contact out of sync

Just had to deal with a hybrid office 365 environment, so on prem AD was syncing to office 365 – no writeback.

Over the time that dirsync/aad connect has been syncing, OUs have been added and removed. As such some issues have occurred with contacts and users. We then had issues when OUs were added back that contacts and users collided on some data (proxyaddresses).

In some cases we were able to make changes to the objects on prem, and these then sync’d in the next run.

However we had some where the office 365 /Azure AD object had become orphaned. Office 365 thought they were sync’d with on prem AD, and as such the gui (web) interface would not allow us to delete these objects.

This is easy to fix, just need to connect to office 365 using powershell to remove the object.

Install the powershell modules if you don’t have them

  • Install-Module -Name AzureRM -AllowClobber –Force
  • Install-Module MSOnline

Connect to Azure/Office365

  • $credential = Get-Credential
  • Connect-MsolService -Credential $credential
  • Get-MsolContact -objectid fad9c2dd-xxxxx-x-xxxx-x-x-x-x-x-x-x
  • Remove-MsolContact -objectid fad9c2dd-xxxxx-x-xxxx-x-x-x-x-x-x-x


Thursday, August 03, 2017

OneDrive for Business - alternative email used in user look up for sharing

Hi All,
Just run across this issue, whilst trying to test OneDrive sharing setup.

Was trying to send myself a test share from within OneDrive for Business, however on typing in my home email address OneDrive helpfully looks this up in the Office365 directory, and returns my work email???!! Couldnot understand why this was happening. Tried some other colleagues and used their personal emails, same issue.
In the end I made a support call to Microsoft to understand what was going on.

It turned out to be an alternative email setup on the user account. Why Office365 is using this to look up a user, and then defaulting to the business account is beyond me.
Talking to the engineer MS are aware of the issue, so hopefully they will fix this and remove the alternative email from the lookup.

In the interim, the work around is to go to the user details in Office365, edit the role and then change the alternative email to another email. Yup this is a pain but at the moment there is no way 
to override this.
My community posting about this.
MS Support article for OneDrive Sharing


Tuesday, May 30, 2017

Check Backup\Restore status

When running the AG wizard it is not clear what the percentage completion figure is of each stage.

Use the query below to examine the status of backup and restore operations.

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')





Tuesday, May 23, 2017

Central Management Servers - HA


Recently setup a new Central management Server for SQL Server, whilst doing this I was hoping to use HA, however CMS stores its config in the msdb database, and as such is not able to be HA’d… Sad smile

So… I started looking at a way for me to utilise HA to achieve this.

In the end I came up with using Always On listeners and powershell script to sync between two servers.

Because you can’t add a CMS server to the list, I decided to create a very small Always on environment to just deal with the CMS. I am not going to go through how to setup Always on or how to setup named instances.

Two servers and one instance.

  • DEV-DB01\CMS
  • DEV-DB02\CMS.

The listener was set on


So by doing this DEV-DBCMS would point at whatever server was the primary server. Connecting SSMS to the DEV-DBCMS, would point it at either DEV-DB01 and DEV-DB02 changes made in CMS would only appear on the servers DEV-DBCMS was pointing at.

So I came up with a PowerShell script that will use the DEV-DBCMS as the source of truth (and this would be whatever the primary server was in the HA), and then sync to the other server (in fact it syncs to itself as while, but makes no changes as it will always match). The PowerShell script is run every 5 minutes and from what I can see is working very well. This is not ideal as there is the possibility that changes can be lost if a failover occurs before a change to CMS has sync’d, but that should not occur that often and the benefit of having the CMS always online in some manner is more important.

This script is a modification on some CMS scripts developed by Chrissy LeMaire

I used the following command in SQl Server job to run the powershell script on a schedule of 5 minutes. I had the same job running on both servers in the HA, with jobs staggered by 2 minutes.

SQL Server Job Command

powershell –ExecutionPolicy Bypass -Command C:\Scripts\CMS_SyncToServer.ps1 dev-dbcms dev-db01\cms; powershell –ExecutionPolicy Bypass -Command W:\Scripts\CMS_SyncToServer.ps1 dev-dbcms dev-db02\cms;

Powershell Script

param([string]$fromcms, [string]$tocms)

$cr = "`r`n"

Write-Host "######################################$cr" -ForegroundColor Red
Write-Host "Starting Sync for $fromcms to $tocms $cr" -ForegroundColor Red
Write-Host "######################################$cr" -ForegroundColor Red

#For SQL 2012 Microsoft.SqlServer.Smo.dll Client Tools SDK is needed
#For SQL 2012 Servers, Microsoft.SqlServer.Management.RegisteredServers.dll must be copied from an newer version of SQL Server (2016 etc)

#Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'
#Add-Type -Path 'C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Management.RegisteredServers.dll'

#For older versions of powershell.

$fromserver = New-Object Microsoft.SqlServer.Management.Smo.Server $fromcms
$toserver = New-Object Microsoft.SqlServer.Management.Smo.Server $tocms
try {
    $fromcmstore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($fromserver.ConnectionContext.SqlConnectionObject)
    $tocmstore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($toserver.ConnectionContext.SqlConnectionObject)
catch { throw "Cannot access Central Management Servers$cr" }
Function Parse-ServerGroup($fromserverGroup, $toservergroup)
    # Remove Servers
    foreach ($instance in $toservergroup.RegisteredServers)
        $instancename = $
        if($ -notcontains $instancename)
            Write-Host "Deleting Server $instancename$cr" -ForegroundColor Red
            $oldserver = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer($toservergroup, $instancename)
            $oldserver.ServerName = $instance.ServerName
            $oldserver.SecureConnectionString = $instance.SecureConnectionString
            $oldserver.ConnectionString = $instance.ConnectionString
            $oldserver.Description = $instance.Description
            Write-Host "Deleted Server $servername$cr" -ForegroundColor Red
    # Add Servers
    foreach ($instance in $fromserverGroup.RegisteredServers)
        $instancename = $
        if($ -notcontains $instancename)

            if ($instance.ServerName.ToLower() -eq $tocmstore.DomainInstanceName.ToLower())
                Write-Warning "Server name is Central Management Server name. Add prohibited. Skipping.$cr"

            Write-Host "Adding Server $instancename$cr"
            $newserver = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer($toservergroup, $instancename)
            $newserver.ServerName = $instance.ServerName
            $newserver.SecureConnectionString = $instance.SecureConnectionString
            $newserver.ConnectionString = $instance.ConnectionString
            $newserver.Description = $instance.Description
            Write-Host "Added Server $servername$cr" -ForegroundColor Green
            Write-Warning "Server $instancename already exists. Skipped$cr"

    # Add Groups
    foreach($fromsubgroup in $fromserverGroup.ServerGroups)
        $tosubgroup = $toservergroup.ServerGroups[$]
        if ($tosubgroup -eq $null)
            Write-Host "Creating group $($$cr" -ForegroundColor Green
            $tosubgroup = New-Object Microsoft.SqlServer.Management.RegisteredServers.ServerGroup($toservergroup,$
        Parse-ServerGroup -fromserverGroup $fromsubgroup -toservergroup $tosubgroup

foreach ($element in $array) {

foreach ($serverGroup in $fromcmstore.DatabaseEngineServerGroup)
    Parse-ServerGroup -fromserverGroup $serverGroup -toservergroup $tocmstore.ServerGroups[$]