Categories

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

 

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016

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

function get-database {
param([string]$sqlbox="")
## 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
$server
.databases
}

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.

Enjoy

One Response to PowerShell Deep Dive: VII using SMO

Leave a Reply