Art of the DBA Rotating Header Image

#Powershell and SQL Server: Using the SQLPS Provider

In the previous post, I gave an overview of what the SQLPS provider is and how it works. Now I want to pull back the curtains a bit and demonstrate how you can navigate the provider and use it effectively for managing your SQL Server instances. For those new to Powershell, this can be an effective way to start using the language without a lot of “overhead” for learning syntax and methods.

Being Direct

Focusing on the concept that providers allow us to browse parts of the Windows stack as directory structure, let’s start by just reviewing what “directories” are available with SQLPS:

SQLPS_use_1

Right away you can see that there’s a lot of familiarity here, as we can see many components of SQL Server that we’re used to seeing. For the time being, let’s go straight into the SQL Database Engine and look around by browsing down into the SQL\LOCALHOST\DEFAULT path:

SQLPS_use_2Pay attention to the structure of the directory paths:

SQLPS_use_3

This pattern is used throughout the provider, offering a consistent structure for our objects. The next thing to notice is that the list of items under our instance is pretty standard and looks very much like what we would find under the object browser in SQL Server Management Studio. The provider is merely an abstraction for all the usual parts of SQL Server that we’re used to seeing.

Objects and Details

The most basic thing we can use the SQLPS provider for is getting lists of our SQL Server objects. Just like getting a directory listing of our files, we can list out our databases with a simple command:

SQLPS_use_4

Note the use of the -force flag. If you leave the -force flag off, the provider will only return user databases. Including this flag will display all databases, including the system databases.

What should also be noted is that there are many more fields here available to us, which we can see using Get-Member. As DBAs, a common question is when our databases were last backed up. Within the SMO, each database has the .LastBackupDate property that shows the last full backup, which we can easily include in our directory call:

SQLPS_use_5

Getting Things Done

While listing objects (and you can list logins, agent jobs, and other parts of your instance in much the same way) is effective, what else can we do? The key to the next step is recalling that objects within the provider are all SMO database objects.  All of these objects will have attendant properties and methods. We can leverage the properties to accomplish some pretty standard tasks.

One thing I tend to do with my databases is set the owner to ‘sa’. This common task is easy to do in T-SQL and I certainly don’t want to discourage that option, but it’s also an easy task to manage in Powershell by using the .SetOwner() method:

cd SQLSERVER:\SQL\LOCALHOST\DEFAULT\databases
$dbs = dir

foreach($db in $dbs){
$db.SetOwner('sa')
}

#List the directory contents again and look at the owner
dir

With these few lines, I can quickly change the owner of all my databases. How cool is that? Now, one note about permissions. Any actions you execute through the provider will use your current security context. Typically, DBAs will have enough permissions to perform these tasks, but if the account you connect to does not have rights for a task, it will fail.

And so much more

There are so many more examples of what we could use the provider for. Simply using directory lookups can provide a lot of flexibility, but there’s more we can do leveraging the methods and properties of the Provider objects. From this point, it can get very advanced and complex. Focus on the fact that SQLPS provider is a starting point, a place where can quickly get in and manage SQL Server with Powershell.

Next up is some of the warts of the SQLPS provider. A lot of folks who have been using the provider have run in to some challenges. In a lot of ways, how the SQLPS provider works is a little clumsy. This doesn’t mean you shouldn’t use it, but I do want to cover some of the frustrations so that you are aware of them and they don’t prevent you from effectively using this aspect of Powershell.

Leave a Reply

Your email address will not be published. Required fields are marked *