Month: July 2012

SQLPSX & Sql Server 2012

For monitoring of our production servers we rely on Powershell, and consequently SQLPSX.  We recently (last night) deployed our first Sql Server 2012 server into production, and apparently our custom powershell monitoring was less than happy with this.  It was gassing on about ‘Error converting data type varchar to uniqueidentifier’ and such. 

Luckily, the fix was pretty easy.  In the SqlServer module for SQLPSX in the SQLServer.psm1 file, there is a reference to the various version 10 smo libraries:

try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}

try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop; $smoVersion = 10}
catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"; $smoVersion = 9}

try
{
    try {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
    catch {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended" -EA Stop}
}
catch {Write-Warning "SMOExtended not available"}

I simply changed these references (make sure you get all three!) to reference version 11 and voila!  Everything is back to normal.  

try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}

try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop; $smoVersion = 10}
catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"; $smoVersion = 9}

try
{
    try {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
    catch {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended" -EA Stop}
}
catch {Write-Warning "SMOExtended not available"}

I’ve not noticed any ill-effects (as of yet) to modifying these libraries.  I’ve not tried modifying any of the versions in any of the other modules either, but I’m pretty confident that changing them would work fine.  MS is extremely good and keeping their API’s backwards compatible.

Obviously, make sure you have the Sql Server 2012 Shared Management Objects (SMO) installed.  You can download them in the Sql Server 2012 Feature Pack.

As always, change at your own risk.  Worked for me, might not for you.

Powershell script to Extract Litespeed Databases

Quick script to decompress a bunch of litespeed backups to their native format.  This uses the extractor that comes with litespeed itself.  This will create a different directory for each uncompressed backup.

cls
$path = "D:\Move"  #where all the compressed ls backups are
$extractor = "D:\Move\Extractor_x64.exe"  #the path to the extractor
$destPath = "D:\Move\Extract"  #where you want the litespeed to create the dirs and place uncompressed backups

#example cmd line for extracting compressed backups
#extractor_x64.exe -F"CompressedBackupName.bak" -E"UncompressedBackupName.bak" -N1

foreach($file in gci -path $path | where{$_.Extension -eq ".bak"})
{
    $dbName = $file.Name.Substring(0, $file.Name.lastindexof("_db_"))
    $extractDir = "$destPath\$dbName"
    mkdir $extractDir -force
    $extractFile = "$extractDir\$dbName" + ".bak"
    $params = "-F`"$($file.FullName)`"", "-E`"$($extractFile)`"", "-N1"
    
    & $extractor $params
}