Working with multiple versions of SMO in PowerShell

 

There are lots of ways to load the SMO assembly in PowerShell.

You can use the PowerShell V1 method:

[void] [Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo');

This loads the most recent version of the assembly. It doesn’t provide a way to specify a version. For these reasons, the method has been obsoleted in the .NET API for a long time.

The PowerShell V2 way is to use the Add-Type cmdlet. According to Max Trinidad, this works well in an environment with one version of SQL Server:

Add-Type -AssemblyName 'Microsoft.SqlServer.Smo';

I don’t work in such an environment, so the above command fails with an error.

You can specify the version to the Add-Type cmdlet, but you also have to specify the Culture and the PublicKeyToken of the underlying assembly.

I don’t know much about how .NET assemblies work. I do know that for this particular assembly, the Culture is always neutral, and the PublicKeyToken is always 89845dcd8080cc91.

Because the only attribute that varies is the version number, we can use this simple function to load an assembly of a specific version:

function Add-Smo ($Version) {

  Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=$Version, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

};

 

Add-Smo -Version '10.0.0.0';

 

New-Object 'Microsoft.SqlServer.Management.Smo.Server';

In my environment, the first few lines of output of the above script looks like this:

 

 

AuditLevel                  : Failure

BackupDirectory             : C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup

BrowserServiceAccount       : NT AUTHORITY\LOCAL SERVICE

BrowserStartMode            : Disabled

BuildClrVersionString       : v2.0.50727

BuildNumber                 : 5500

Collation                   : Latin1_General_CI_AS

CollationID                 : 53256

ComparisonStyle             : 196609

...

In my environment, the valid values of the Version parameter are ‘10.0.0.0’ and ‘11.0.0.0’. For you, it may vary.

To use this in another script, you might just want to copy the function body and hard-code the version number you need.

I don’t know how to create PowerShell function libraries yet, but I might like to add this to my own library of SQL Server functions.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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