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:
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 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:
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.
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 –
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
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
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
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
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
The UK PowerShell group presents a Live Meeting tomorrow on using PowerShell with SQL Server
First reminder for the UG meeting on 28February – PowerShell and SQL Server