Monthly Archive


PowerShell and SQL Server

PowerShell Deep Dive: VII using SMO

SMO  =  (SQL) Server Management Objects.  They first shipped with SQL Server 2005 and continued into SQL Server 2008. They are .NET classes that enable us to manage SQL Server systems programmatically – in our case from PowerShell. The SQL Server Management Studio in SQL Server 2005 and above is built on SMO.

One of the talks at Deep Dive was about discovering the Recovery Model across a number of SQL Server 2000/2005/2008 systems. They were using the SQL Server PowerShell provider that ships with SQL Server 2008 to access SQL 2005 and SQL 2008 systems. A different method was used for SQL Server 2000.

I asked why a simple SMO script wasn’t run remotely against all of the servers. I was told that SQL Server 2000 doesn’t support SMO therefore they couldn’t.

I didn’t think this was correct but didn’t have an environment available in which I could test it.

I’ve since managed to find time to test the idea


## get-database
## This uses the SMO assemblies to retrieve database
## Richard Siddaway May 2010

function get-database {
## load SMO assemblies
## use $null to prevent display of assembly load information

$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
#$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

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

This simple function loads the SMO assemblies. If you are running on SQL Server 2005 then uncomment SmoEnum and comment out SmoExtended.

The function connects to the specified server and retrieves the database collection.  We can simply find the recovery model like this

get-database Asql2008-server | ft Name, RecoveryModel -a                                                                                                       
get-database Asql2005-server | ft Name, RecoveryModel -a                                                                                                       
get-database Asql2000-server | ft Name, RecoveryModel -a   


So – YES we can access SQL 200, 2005 and 2008 with SMO. Simply install the SQL Server client tools from SQL Server 2005 or 2008 onto your workstation and you can access all SQL server 2000, 2005 and 2008 systems in your environment.


PowerShell Tech tips

Two tech tips that I’ve contributed to the Realtime publishers site are now available

Go to

and look under Disaster Recovery for

How to Configure a Windows Server 2008 R2 Failover Cluster with Windows PowerShell


How to Configure Data Mirroring in SQL Server 2008 with Windows PowerShell



SQL Server backup

An article I wrote for Windows Administration in Realtime has been posted on the Realtime Publishers tech tips site

then look for

Solidifying Your Backup and Recovery Srategy for SQl Server

It shows an example of using PowerShell to backup databases

Technorati Tags: PowerShell,SQL Server

SQL Server PowerShell Extensions

If you use PowerShell with SQL Server you will want to check out the 2.0 release of the SQL Server PowerShell Extensions from

To quote the release notes

Release 2.0 re-implements SQLPSX as PowersShell version 2.0 modules. SQLPSX consists of 7 modules with 132 advanced functions, 2 cmdlets and 7 scripts for working with ADO.NET, SMO, Agent, RMO, SSIS and SQL script files. In addition an optional component of a backend database and SQL Server Reporting Services 2008 reports are provided. See readme file for details.”

Well worth a look

Technorati Tags: PowerShell,SQL Server

SQL Server books

A couple of SQL Server books that I would recommend having been involved with both of them.

First up is SQL Server 2008 Administration in Action by Rod College (Manning - which I reviewed several times before publication. It covers SQL Server from sizing and installation, through configuration and day-to-day administration.  Lots of good best practice information written in a easy to read style.

The second is SQL Server MVP Deep Dives (Manning - to which I contributed a chapter on PowerShell and SQL Server. This book has contributions from 53 MVPs (mainly SQL Server) and is edited by Paul Nielsen, Kalen Delaney, Greg Low, Adam Machanic, Paul S. Randal, and Kimberly L. Tripp.  The royalties from the book go to War Child International - (

Two good books to add to your SQL Server library

Technorati Tags: Books,SQL Server

SQL Server autoclose

Buck Woody blogged about SQL Server autoclose and how it should be set to being off  - 

I’m doing a lot with SQL Server and PowerShell at the moment and it started me thinking that I could easily set this property in PowerShell.

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server("SQL08")
$Server.databases | select Name, Autoclose
foreach ($db in $Server.Databases){
    if (!$db.IsSystemObject){
        $db.Autoclose = $false
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server("SQL08")
$Server.databases | select Name, Autoclose


Create a server object and display the databases, selecting name and the autoclose property.  Its boolean so we get true or false back.

We can iterate through the database collection. Test if the database is a system database and set autoclose to false (we don’t want autoclose to happen).  A call to Alter() saves the change.

Finally we can recreate the server object and  redisplay the autoclose property.

Technorati Tags: PowerShell,SQL Server

PowerShell for DBAs

Chad has a very interesting post on “The Value Proposition of PowerShell to DBAs” -!EA42395138308430!347.entry where he discusses the results of a poll of DBAs regarding PowerShell.

On initial reading it is a bit depressing for the PowerShell community as only 20% of respondents were using PowerShell. However, it gets a bit more cheerful if you consider that another 40% were planning to – I wonder how that will change as SQL Server 2008, with PowerShell built in, becomes more widespread.

Chad gives a number of benefits of learning PowerShell. I think that one of the most compelling reasons si that it will be a part of all future Microsoft products – look what is happening with Windows 2008 R2 – an provides a common automation platform across your Microsoft estate. PowerShell gives us the possibility of integrated, automated administration across you servers and applications.

Gives us more time for PowerShel space invaders

Technorati Tags: PowerShell


If you use PowerShell and SQL Server you should at the very least look at SQLPSX which is a big library of PowerShell functionality.  Version 1.5 has just been released.  See!EA42395138308430!315.entry for details


Technorati Tags: PowerShell,SQL Server

Compare Database Schemas

One of the great things about PowerShell is the strength of the PowerShell community. This is shown by the number of people sharing their PowerShell scripts. 

A good example is the script to compare the schemas of two SQL Server databases that Steven has written -  This could be valuable if you work in an environment where databases are frequently changing.  This can also be used to give an audit trail of change over time.

It is also a good example of how to interact with SQL Server from within PowerShell.


Technorati Tags: PowerShell,SQL Server

SQL Server scripts

Idera have published a set of PowerShell example scripts for working with SQL Server.  They can be downloaded (free) from   Check the bottom of the products menu for PowerShell scripts.

They give a good introduction to working with Powershell and SQL Server – recommended.


Technorati Tags: PowerShell,SQL Server