SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

A Month of PowerShell – Day 10 (Navigating SMO collections)

Welcome to Day 10 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at http://blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

Yesterday, a couple of different methods were utilized to navigate the SMO collections. Before we dig deeper into SMO, let’s first take a look at the two different ways of navigation SMO collections within PowerShell.


When the sqlps module is added in PowerShell, one of the methods of navigating SQL Server objects is with the PSDrive SQLSERVER: When utilizing the SQLServer drive, by default system objects are not included when you run Get-ChildItem  (or in a tab completion list). In order to return system objects, set the following variable to true:

$SqlServerIncludeSystemObjects = $true

You can get further help with the SQLServer provider by running Get-Help SQLSERVER

SQLSERVER: Collections

If we run Get-ChildItem SQLSERVER:\SQL\localhost\SQL2012, we will get the collections at the server level that we can work with:



To get a list of backup devices and paths, enter:

Get-ChildItem SQLSERVER:\SQL\localhost\sql2012\BackupDevices | `
    Select-Object Name, PhysicalLocation

To add a new backup device:

$server = New-Object ('Microsoft.SqlServer.Management.SMO.Server') ".\SQL2012"
$bd = New-Object -TypeName Microsoft.SqlServer.Management.Smo.BackupDevice
$bd.Parent = $server
$bd.Name = "My_New_Backup_Device"
$bd.PhysicalLocation = "D:\MSSQL\SQL2012\Backups\MyBackupFile.bak"
$bd.BackupDeviceType = "DISK"

(Verify the backup device has been created in SSMS. Run the first script above to verify that PowerShell sees the backup device.)

To delete the backup device, you can use either of the following:

Remove-Item SQLSERVER:\SQL\localhost\SQL2012\BackupDevices\”My New Backup Device”


$server = New-Object ('Microsoft.SqlServer.Management.SMO.Server') ".\SQL2012"
$bd = $server.BackupDevices["My New Backup Device"]

To return all of the endpoints running on the server:

$SqlServerIncludeSystemObjects = $true
Get-ChildItem SQLSERVER:\SQL\localhost\SQL2012\Endpoints


$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server "localhost\SQL2012"

To get all of the logins on this server, any of the following will work:

$SqlServerIncludeSystemObjects = $true
Get-ChildItem SQLSERVER:\SQL\localhost\SQL2012\Logins


$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server "localhost\SQL2012"


$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "localhost\SQL2012"

(The last two demonstrate different ways of utilizing the New-Object cmdlet with the same class.)

To return the server roles:

Get-ChildItem SQLSERVER:\SQL\localhost\SQL2012\Roles


$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "localhost\SQL2012"

You might notice the two different ways that I have specified the server to connect to. When using the New-Object cmdlet, I have used either “localhost\SQL2012″ or “.\SQL2012″, and when using the SQLSERVER: drive, I use “localhost\SQL2012″. As it turns out, the SQLSERVER drive won’t work when using the “.” shortcut for localhost. Throughout the code in the rest of the series, I will typically use the “localhost\SQL2012″ format so that it can be utilized in string substitution with the SQLSERVER drive.

From today’s post, you can see that there are two different ways to navigate a server using PowerShell – with the New-Object cmdlet at the server level, or with the SQLSERVER PSDrive provider. The SQLSERVER PSDrive doesn’t always provide a way to create a new item (for instance, the backup device earlier), but it can be easier to list / delete the items in a collection that way. With either method, you can assign collections and properties to variables, and navigate the collection.


Leave a comment on the original post [blog.waynesheffield.com, opens in a new window]

Loading comments...