September 2012 – Max Trinidad – The PowerShell Front

There’s no need to install SQL Server to create PowerShell SMO scripts. So, how I do this? You need three stand-alone packages from the “Microsoft® SQL Server® 20xx Feature Pack”. Notice the “xx” in the SQL Server version. This is because the
“feature packs” has been available since SQL Server Version 2005. In my scenario I decided to use the “Microsoft® SQL Server® 2012 Feature Pack”: http://www.microsoft.com/en-us/download/details.aspx?id=29065

You need to installed them in the following order:

Microsoft® System CLR Types for Microsoft® SQL Server® 2012
Microsoft® SQL Server® 2012 Shared Management Objects
*Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012

Make sure to pick the right package for your system: (x86) or (x64).

When you’re done with the installation then you are ready to start scripting with SMO. Also, you will notice that the SQL Server SQLPS module will get installed in your system.

Use the “Get-Module -ListAvailable” command and look for the SQLPS module in the bottom of the screen.

Now we can create the following PowerShell SMO script:

[sourcecode language=”powershell”]
## – Loading the SQL Server SMO Assembly"
[System.Reflection.Assembly]::loadwithPartialName("Microsoft.SQLServer.SMO");

## – Build SQL Server object connecting using default Windows Authentication:
$SQLSvr = ‘WIN2K3SQL1’;
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQLSvr;

## – Changing from default Windows to SQL Authentication:
$MySQL.ConnectionContext.LoginSecure = $false;
$MySQL.ConnectionContext.set_Login(‘User1’);
$SqlUPwd = ConvertTo-SecureString ‘$Pwd01!’ -AsPlainText -Force;
$MySQL.ConnectionContext.set_SecurePassword($SqlUpwd);

## – Get some information:
$MySQL.Information | Select-Object Parent, Version, Edition | fl;
[/sourcecode]

Here’s the results:
Notice I installed my three “SQL Server 2012 feature” packages and created to use a PowerShell SMO script to access a SQL Server 2000 Engine. But, Keep In Mind, that SQL Server SMO 2012 will work for most parts to extract information off SQL Server 2000. Just by trial and error you will notice some limitations. Also, although I have the SQLPS module available but I didn’t have to use it yet.