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.

SQLSERVER:

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:

Source code

$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:

Source code

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

To add a new backup device:

Source code

$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"
$bd.Create()

(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:

Source code

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

Or:

Source code

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

To return all of the endpoints running on the server:

Source code

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

Or:

Source code

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

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

Source code

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

Or:

Source code

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

Or:

Source code

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

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

To return the server roles:

Source code

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

Or:

Source code

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

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.