PowerShell is a great tool that allows DBAs and Developers alike to script, manage and automate SQL Server tasks. Most of the objects and tasks will require SMO (SQL Server Management Objects).

Basic Steps

1. Set Execution Policy

Depending on what you need to do, you may need to set the Execution Policy in your Powershell console. In my case, I will be using it to access SQL Server for administrative purposes, so I will be setting mine to have Unrestricted access to my server. Make sure you review the security guidelines in your organization before you set your environment to Unrestricted

PS> Set-ExecutionPolicy Unrestricted

2. Load assemblies

First, you will need to load SQL Server related assemblies. For SMO, you will usually want to load the following assemblies:

• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.SqlEnum
• Microsoft.SqlServer.SmoEnum
• Microsoft.SqlServer.ConnectionInfo

When you load the assemblies, you have to use the following format:

[Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, `
      Version=9.0.242.0, Culture=neutral, ` 
      PublicKeyToken=89845dcd8080cc91")

A couple of things to note here:

• the backtick is the line continuation character, ie if your line of code spans multiple lines, you have to use a backtick
• you can get the version of the assembly either by :
  • going to the GAC (C:Windowsassembly), right click on assembly and click on properties
  • use reflector

3. Create Server Object

You will need to create a server object

# ===================================
# if local instance
# ===================================
$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server
 
# or
 
$serverName = "(local)"
$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server ` 
          -argumentList "$serverName"
 
 
# ===================================
# if named instance, use format SERVERNAMEINSTANCENAME
# ===================================
$serverName = "JUBILEESQL01"
$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server ` 
          -argumentList "$serverName"

4. Connect

Once you have created a SQL Server Object, you can connect to SQL Server using either windows (trusted) or SQL authentication.
This also shows you how you can use the Get-Credential cmdlet

# ===================================
# Alternative 1: Trusted Connection
# ===================================
$server.ConnectionContext.LoginSecure = $true
 
 
# ===================================
# Alternative 12: SQL authentication, using Get-Credential cmdlet 
# which prompts you for a username/password when you run the script
# ===================================
$server.ConnectionContext.LoginSecure = $false
$credential = Get-Credential
 
# remove leading backslash in username, assuming no domain name was supplied
$userName = $credential.UserName -replace("\","")
$server.ConnectionContext.set_Login($userName)
$server.ConnectionContext.set_SecurePassword($credential.Password)
 
 
# ===================================
# Alternative 3: SQL authentication, supplying username 
# and password in the script, something you should be 
# *very* wary of doing
# ===================================
 
# also note here that we need to convert the password to 
# a SecureStriing, before we can pass it to set_SecurePassword
 
$server.ConnectionContext.LoginSecure=$false;
$server.ConnectionContext.set_Login("JDoe")
$securePassword = ConvertTo-SecureString "mypassword" -AsPlainText –Force
$server.ConnectionContext.set_SecurePassword($securePassword)

Here’s a complete sample.

All this script does is connect to a SQL Server named instance using SQL authentication, and just display the connection string.

# load assemblies
[Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, `
      Version=9.0.242.0, Culture=neutral, ` 
      PublicKeyToken=89845dcd8080cc91") 
[Reflection.Assembly]::Load("Microsoft.SqlServer.SqlEnum, `
      Version=9.0.242.0, Culture=neutral, `
      PublicKeyToken=89845dcd8080cc91") 
[Reflection.Assembly]::Load("Microsoft.SqlServer.SmoEnum, `
      Version=9.0.242.0, Culture=neutral, `
      PublicKeyToken=89845dcd8080cc91") 
[Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, `
      Version=9.0.242.0, Culture=neutral, `PublicKeyToken=89845dcd8080cc91")
# connect to SQL Server named instance
# server name is JUBILEE
# instance name is SQL01
# use serverinstancename
$serverName = "JUBILEESQL01" 
$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$serverName"
# login using SQL authentication, which means we supply the username
# and password
$server.ConnectionContext.LoginSecure=$false;
$credential = Get-Credential
$userName = $credential.UserName -replace("\","")
$server.ConnectionContext.set_Login($userName)
$server.ConnectionContext.set_SecurePassword($credential.Password)
# clear the screen
cls
# list connection string
Write-Host "--------------------------------------------------------"
Write-Host "Connection String : "
Write-Host $server.ConnectionContext.ConnectionString
Write-Host "--------------------------------------------------------"

By the way, I am using Idera’s PowerShell Plus editor/console. I’ve tried writing all my scripts using a regular text editor before, and I’m pretty confident when I say nothing beats having an awesome tool when you do your job. A few very remarkable features I really like:

• Intellisense – can anyone argue with this?
• Editor/Console combo – I write my script, press F5, and it runs!
• Built in Powershell Help/Reference
• Debug feature

Idera also provides a PowerShell video series by Don Jones, and free Powershell scripts.

Be Sociable, Share!

• 
•