Microsoft® SQL Server® 2012 Bible

786

Part V: Enterprise Data Management

You work with the SMO every time that you navigate Object Explorer in SSMS, so you’re

already somewhat familiar with its use, some of its capabilities, and its hierarchy. In addi-

tion to Object Explorer, the Object Explorer Details view (press F7) inside of SSMS is great

for getting more familiar with SQL Server objects and their properties.

Before using SMO within PowerShell, the SMO assembly must be loaded into the environ-

ment. If PowerShell is started from within SQL Server Management Studio or from SQL

Server Agent, the sqlps.exe program is run, not full blown PowerShell. When running the

native PowerShell environment interactively, the following commands are required:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft

.SqlServer.SMO') | out-null

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft

.SqlServer.SMOExtended') | out-null

If SMO objects are going to be loaded on a regular basis, these commands can be loaded

into a profi le fi le. User profi les are generally loaded into $PSHome\ Documents\

WindowsPowerShell\Microsoft.PowerShell_profile.ps1, where $PSHome returns

the current user’s fi le path like c:\Users\username. The easiest way to open the current

user’s profi le is to type notepad $profile inside of PowerShell.

The results of the command are piped to the out-null device because the version number

of the library is generally not needed.

After loading the SMO libraries, it’s easy to connect to a server (using Windows

Authentication) by issuing the following command:

$sqlsvr = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer'

The $sqlsvr variable now contains a Server object for the MyServer instance of SQL

Server. The properties and methods of the Server object can be seen by piping the vari-

able into the Get-Member cmdlet:

$sqlsvr | Get-Member

The SMO object library is best represented in a chart for ease in understanding. The basic

object is the Server object, and managing the server starts with connecting to that

object. For example, the SMO objects used in managing user databases are shown in

Figure 30-2.

Creating databases and database objects using SMO may seem counterintuitive because

usually these objects are created using Transact-SQL scripts, but automating the

processes that create the objects can provide consistency in an area that is usually quite

inconsistent.

c30.indd 786c30.indd 786 7/31/2012 9:46:23 AM7/31/2012 9:46:23 AM

http://www.it-ebooks.info