In the last post we covered the cmdlets that are used to acquire, start, and stop SQL Instances. Naturally, the next area to talk about are cmdlets designed for SQL databases. Let’s see what we have using Get-Command again:
Get-Command -Module SQLPS *SqlDatabase
Three cmdlets are available, each one easily understandable from their names. We’ll start by looking at Get-SqlDatabase’s help file (using Get-Help Get-SqlDatabase -ShowWindow). Fortunately, this is one of the better documented cmdlets, though I’m still disappointed that the help information doesn’t show the output object type.
I know I sound like a broken record, but it is so vital to remember that everything in Powershell is an object. This means that knowing what our output objects are is key to being able to use our cmdlets. What we need to do now is use Get-Member to investigate the output of the cmdlet:
Get-SqlDatabase -ServerInstance PICARD -Name AdventureWorks2012 | Get-Member
Our output is an Smo.Database object, with all of those methods and properties. As we previously covered, this is the same object that the provider gives you. This means the Get-SqlDatabase cmdlet is functionally equivalent to:
Get-Item SQLSERVER:\SQL\PICARD\DEFAULT\Databases\AdventureWorks2012
This means we have a couple different paths to the same result, so whichever technique is used is a matter of preference. Interestingly enough, the Get-SqlDatabase cmdlet takes a -Path argument, but only works for the instance. We can not use the -Path to define the database, which seems weird to me. Anyway, this command will use the path and result in the same output:
Get-SqlDatabase -Name AdventureWorks2012 -Path SQLSERVER:\SQL\PICARD\DEFAULT
Another little gotcha with this cmdlet has is there’s a -Script switch argument for the cmdlet. Typically, when you see the -Script argument, this is a way for the cmdlet to call the .Script() method and produce a T-SQL script for the action. The thing is, Get-SqlDatabase is not actually performing a T-SQL action, just acquiring a SMO object. If you use the -Script argument, nothing will get returned because of this.
One significant difference between getting the option via the cmdlet versus the path and Get-Item is that Get-SqlDatabase will take a PSCredential object for -Credential. This means we can use alternate authentication for getting our objects. While we know we can do this using the provider, this can save us a step.
There’s a lot of variations here and we would use this cmdlet in any situation where we wanted to get the database object. What I do want to call out is the disconnection between Get-SqlDatabase and Get-SqlInstance. The fact that Get-SqlInstance returned a different type of object than the Smo.Server class when everything else with the provider works using SMO objects makes it a little weird. At least Get-SqlDatabase maintains that consistency with the SMO, giving us a handy way to manage and manipulate database objects for our scripts.
I’m tweeting!