Monthly Archive


PowerShell and SQL Server

Capacity planning series finished

My capacity planning series on the Scripting Guy blog finished last week. Didn’t get chance to post about it as I was at Microsoft in Seattle.

Full series and associated powertip postings:


Capacity Planning part 2

My capacity planning mini series on the Scripting Guy blog continues with the second part – dealing with storing data in SQL Server -  available today

I’ll repeat the URL for the first part for reference

Loading the SQLPS module

Loading the SQLPS module gives you quick access to the sql cmdlets, sql provider and the SMO assemblies. It also, infuriatingly, moves your location into the sql provider.

I (really) *  (lots ) hate things that assume what I want to do.

You can use PowerShell to reverse this annoying, and arrogant action:


Import-Module SQLPS


Will get you back where you were.

SQLPS is slowly becoming a civilised member of the PowerShell eco-system especially when compared to its first incarnation.  A few more versions and it might behave properly.

SMO with SQL Server 2012

I’ve been working quite a bit with PowerShell and SQL Server 2012 just recently. In earlier versions of SQL Server you had to load the SMO assemblies to get access to the admin functionality.

In SQL Server 2012 –

import-module sqlps

gives you the SQL Server provider, the cmdlets and loads the SMO assemblies for you.

Nice and easy so I don’t have to do anything extra. That’s a good module

Dropping a database

A question came up on the forum regarding dropping a database & I realised it was something I hadn’t done before.

SMO provides a set of classes for managing SQL Server. You get SMO when you install the SQL Server management tools

Import the module to load SMO assemblies

import-module sqlps

get the server object and view the databases
$server = New-Object  Microsoft.SqlServer.Management.Smo.Server("w12scorc")

view the target database

drop the database and view the databases again

PowerShell in Practice offer today only

Manning have PowerShell in Practice on a half price offer today. Go to and use code dotd0330cc when ordering

UK PowerShell group–February 2012 recording

The recording, slides and demo scripts from tonight’s PowerShell and SQL Server session are available as a single zip file for download from

The file is created with jzip but any zip handling program should be able to unzip it

Thank you to the attendees and especially for the questions – I’ve posted the answer to the question regarding accessing named instances using a port number here

Next meeting will be 28 March 2012

Topic will probably be CIM in PowerShell v3

Connecting via SMO to a named instance

A question came up in tonight’s User group session regarding connecting to SQL server instances using SMO

If you have just a default instance – just give the server name

$server = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList "W08R2SQl12"


If you have a named instance the give the instance name as well

$serverI = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList "W08R2SQl12\instance_name"


These work as long as the SQL Server browser service is running. 

If it isn’t this is what I think you have to do.

This is untested and a best guess. I will try and test.

$cons = "server=W08R2SQL12\instance_name,port_number;Trusted_Connection=true;multipleactiveresultsets=false"

$cn = New-Object -TypeName "System.Data.SqlClient.SqlConnection" -ArgumentList $cons
$serverZ = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList $cn
If anyone manages to test this please let me know – I’ll test as soon as I can

February UG meeting–Final reminder


The UK PowerShell group presents a Live Meeting tomorrow on using PowerShell with SQL Server

Details from

UG meeting reminder

First reminder for the UG meeting on 28February – PowerShell and SQL Server

details from