Summary: Guest blogger, Mike Fal, talks about leveraging the SQL Server PowerShell provider.

Microsoft Scripting Guy, Ed Wilson, is here. Welcome back guest blogger, Mike Fal. He blogged yesterday (see Getting Information from the SQL Server Provider with PowerShell), and he continues today…

In my previous post, I covered how to gather and view data for our SQL Server components by using the SQL Server provider. Gathering this information is simple, just like looking up files in a directory. Being able to grab collection of SQL Server objects and display their properties exposes things quickly and easily for PowerShell users.

Using collections with the provider

The fact that we are using collections is the second strength of using the provider. Collections are a key element to automation, mostly because we can use them with loops and process them as part of an automated task. We can combine this with the file system of the SQL Server provider, for example, to easily gather and process collections of databases, logins, or other objects.

Let’s look at the today’s PowerTip, Use PowerShell to Back Up System Databases. We’re actually dealing with two collections here: the instances and the system databases for each instance. The outer loop steps through each instance in that array—that’s easy enough to understand. Within that, we first use the provider to create a collection of the non-TempDB system databases, then use ForEach-Object to run a Backup-SQLDatabase command for each of those. Five lines of code will back up system databases locally for as many instances as we want.

There’s so much more that we can do. One thing I like to do is to have all my database owners set to ‘sa’. It can be tricky to manage even a handful of databases across different servers. We can use the provider to find these:

$instances = @(‘KIRK’,’SPOCK’,’PICARD’,’RIKER’)

$instances | ForEach-Object {Get-ChildItem SQLSERVER:\SQL\$_\DEFAULT\Databases} |
Where-Object {$_.DatabaseOwner -ne 'sa'}

This is the same sort of pattern I explained previously, but what if I wanted to correct this? Using the provider, accessing the methods, and a little pipeline magic will sort this out:

$instances = @(‘KIRK’,’SPOCK’,’PICARD’,’RIKER’)

$instances | ForEach-Object {$_.SetOwner('sa')}

How cool is that? We can really flex our automation muscles thanks to the .NET object model in PowerShell.

Let’s take this one step further. Database professionals like to script schema objects. There are ways to do this through SQL Server Management Studio, but that requires a lot of clicking, and it is not something you can automate. What if you wanted to script all your stored procedures regularly across all your databases? The SQL Server provider and the SMO object model give us the tools to do this.

Let’s start by getting all the user databases:

$dbs = Get-ChildItem SQLSERVER:\SQL\PICARD\DEFAULT\Databases

The SMO.Database object has a property that is a collection of stored procedures, so we can leverage this as another collection for processing. Further investigation shows that the stored procedure object has a .Script() method, which generates a T-SQL script for the object. We plug-in a call for the object collection, pipe it to a file, and we’re good to go:

$dbs = Get-ChildItem SQLSERVER:\SQL\PICARD\DEFAULT\Databases

$dbs.StoredProcedures |

    Where-Object {$_.IsSystemObject -eq $false} |

    ForEach-Object {$_.Script() | Out-File "C:\Temp\$($_.Parent.Name)-$($_.Name)_StoredProcs.sql"}

These patterns can go on and on. Because the object model is so flexible, if we can get a collection, we can use the methods and properties. This is the hidden power of the provider: giving us easy access to collections of different objects for SQL Server. Being able to leverage this lets administrators of all flavors build efficient and robust automation for their environments.

Thanks for joining me for this brief trip through the SQL Server provider and what it can do for you. Hopefully you’ve picked up a few tricks to help you manage your processes and solve some of your questions. Thanks also to Ed for letting me share with the Hey, Scripting Guy! Blog audience. For more from me about PowerShell and SQL Server, check out my blog, Art of the DBA: Blending the right and left sides of SQL Server Database Administration, or find me on Twitter at @Mike_Fal.

~Mike

Mike, thank you for sharing your time and knowledge.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy