PowerShell SQL Server SMO Simplicity Series – 1

This series is a learn by sample blog which I’m hoping any DBA and/or SQL Developer will take advantage to re-use these code snippets.  Due to the lack of SQL Server cmdlets, I prefer to use SMO which gives me flexibility to build my own PowerShell commands to automate most of my routine tasks.

1 – Connecting to your SQL Server engine

This take at least three lines of code: (excluding the comment lines)

## - Setting variables with Server and Database name:
$SQLInstanceName = 'YourSQLServerInstancename';

## - Loading SMO .NET Assembly: (Required)
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;

## - Connecting to SQL Server (Windows Authentication) and building you table object:
$MySQL = new-object('Microsoft.SqlServer.Management.Smo.Server') SQLInstanceName;

The first line you create a variable holding the SQL Server instance name, then loads the SQL Server assembly “Microsoft.SQLServer.SMO“, and then creating the variable holding the SQL Server objects.

The third line it’s what connect to the server using Windows Authentication.  Please be advice that, even you don’t have a Windows Authentication, it will not returned an error and it will return a Null object.

So, if you need to use SQL Server Authentication, you will need to add the following code after creating the “$MySQL” object:

## - Uncomment code block within [#region - #endregion] to enable use of SQL Authentication:
#region - Changing from default Windows to SQL Authentication:

#   ## - Variables required to SQL UserID and SQLPassword:
$SQLUserName = "YourSQLUserID"; $sqlPwd = "YourSQLUSerPassword";

## - Block of Code required to build SQL Authentication:
$MySQL.ConnectionContext.LoginSecure = $false;
$SqlUserPwd = ConvertTo-SecureString $sqlPwd -AsPlainText -Force;

Now, that you’re connected to the SQL Server instance, you can check the content in the variable $MySQL.  To do this the following one-liner help in exploring your object members (Methods and Properties);

$MySQL | Get-member | Out-GridView;


For starters, you might be looking to display some information so you need to look for $MySQL members “Properties” which hold values you can display using the PowerShell “Select-Object” cmdlet.  For example, the following oneliner will display the following database properties: Name, Owner, CreateDate, FileGroup, and PrimaryFilePath.

$MySQL.Databases | Select name, Owner, CreateDate, Filegroups, PrimaryFilePath | Format-Table -autosize;

So, Get-Member is you best command to explore your PowerShell objects and understand its content.  Another very useful command is the “Out-Gridview” which will use more heavily in the next blog series.

Don’t forget for any of these PowerShell cmdlet you can use the “Get-Help” display the command documentation:

Get-Help Get-Member -ShowWindow

Have fun and Keep learning PowerShell!





Post on PowerShell SQLServer SMO simplicity

Stay tuned for a series of post and learn how to start using SQL Server SMO with PowerShell V4 (or greater). Collect information from your SQL Server with PowerShell starting with a few lines of code.  See shortcuts and string formatting working in your favor while manipulating your data.  The scripts will evolved as you learn to apply new techniques.

Yes! PowerShell is about evolution of your automation skills for productivity.

How far can we take this? From this code:

($MySQL.Databases) `
| Select-object Name, Owner, RecoveryModel, Size, DataSpaceUsage, SpaceAvailable `
| Ft -AutoSize;

to an HTML solution.


At the end, it’s all about reusable code.

We glad to welcome our new sponsor Devart with their product “dbForge” which is an excellent tool next to SQL Server Management Studio.  I’ll be briefly showcasing this product.