Gary Bargsley (b|t) is the host of this months
T-SQL Tuesday #110 – ” Automate All the Things”
The question he asks is easy:
What do you want to automate or what automation are you proud of completing?
My answer to that is simple, I’m a major contributor to the awesome Powershell library dbatools. What I’ve contributed to that library are commands that will help automate the running and processing of queries from the DMV library of Glenn Berry
At some point in the life of a DBA we’ve all come accross his scripts. For the longest time I would advise people to google “Glenn Berry DMV”, and it will be the top result.
The scripts however, come in a single file per SQL Server version and you can’t run them all in one go. You would have to select a script, run it, and paste the result from Management Studio into an Excel sheet. Glenn provides an empty sheet with tabs ready to paste the various result sets in. I’ve automated this part, hope you like it!
Let’s get started with something simple and run a single script.
First install dbatools though, and to make things more interesting for later on, install a module called importexcel while you’re at it.
All you need to do after that is open a powershell window and type something like the following:
Invoke-DbaDiagnosticQuery -SqlInstance localhost -QueryName "Configuration Values" | Select -ExpandProperty result | Format-Table -AutoSize
Want to know which scripts are available?
Invoke-DbaDiagnosticQuery -SqlInstance localhost -UseSelectionHelper
So, where is this Excel sheet you promised us?
Invoke-DbaDiagnosticQuery -SqlInstance localhost | Export-DbaDiagnosticQuery -ConvertTo Excel -Path c:\demo
(My laptop is called XPS, it shows that even though I specified “localhost” in the query)
The directory will be filled with a lot of other files besides the Excel sheets. Some of Glenn’s scripts have columns which contain the query or even the query plan. That doesn’t play nice with an Excel sheet so they will be saved in separate files.
Good to know:
- There are options available to exclude the .sql and .sqlplan file from the export
- You can include specific scripts, but also exclude specific scripts
- Want a more portable format? Pipe the result to Export-CliXml. That compresses nicely if you 7zip it and can be used as input for the Export-DbaDiagnosticQuery later on.
- The latest version of Glenn Berry’s DMV scripts are always included in dbatools, you don’t have to download them separately. (but you can certainly do that if you want to for whatever reason)
