Powershell Quick Tips: SQL Server 2008 differences vs SQL Server 2005 when loading SMO GAC’s in PSscripts

First, I found SQL Server 2008 SMO is downward compatible with SQL Server 2005 and limited with SQL 2000.  So, when you build your Powershell SMO scripts, you need to load 2 SMO GAC’s in order for your script work.

Normally, after installing your "Feature Pack for SQL Server 2005" you will have access to SQL SMO Assemblies that will help you manage you SQL Server using Powershell.  The following samples will show you how to basically create a collection of data from the database:

1. Create a collection from an SQL Server 2005, you need to load one GAC and then create the variable that will hold the data:

PS> [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

PS> $MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) "SQLServer2k5"

PS> $MySQL.Information   #access the SQL Server information collection

PS> $MySQL.Databases  #access the SQL Server database collection

By the way, the SMO GAC’s are part of the SQL Server 2008 installation so you will be able to use it with Powershell.  So. if you install SQL Server 2008 in the same Desktop where SQL Server 2005 was installed, then you will be able to manage with SMO all your 2005, 2008 and partially 2000 SQL engines.



About maxt2posh

I’m from Puerto Rico, have been working with computers since 1979. Throughout many years, I have worked with SQL Server Technologies, provided support to Windows Servers/Client machines, Microsoft Virtualization Technologies, and build some Visual Studio solutions. I’m a Microsoft PowerShell MVP since 2009 and MVP SAPIEN Technologies since 2010. I speak in most of the SQLSaturday, IT Pro and .NET camps events around the Florida’s State. Also founder of the Florida PowerShell User Group which meeting every 3rd Tuesday evening of the month.
This entry was posted in PowerShell. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s