Summary: Guest blogger, Mike Fal, talks about leveraging the SQL Server PowerShell provider.
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.
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.
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.
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, thank you for sharing your time and knowledge.