header image

PowerShell Deep Dive: VII using SMO

Posted by: | May 3, 2011 | 1 Comment |

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.


under: Deep Dive, PowerShell and SQL Server

1 Comment

  1. By: Reiner on August 15, 2011 at 3:37 am      


    I needed to do the same.

    I used SQLPSX from codeplex and a function I found here : http://blogs.technet.com/b/sqlthoughts/archive/2008/10/03/out-sql-powershell-function-export-pipeline-to-a-new-sql-server-table.aspx

    Armed with these two I created this oneliner :

    Get-SqlData ‘Name_of_Server’ Database_Name “SELECT ‘column_where_servernames_reside’ FROM tbl_Server_List” | foreach { $srvr = Get-SqlServer $_.column_where_servernames_reside ; Get-SqlDatabase $srvr } | Select Parent, Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable | out-sql -sqlserver servername -database databasename -table dbo.tbl_name -dropexisting $true