PowerShell exposes scads of information and provides the professional with a formidable tool for the tool-belt. Over the past several articles, I have demonstrated how this tool can be used to uncover useful information and metadata.

The groundwork has been laid to be able to mine information about each of the core components for Extended Events Sessions. These core components are Targets, Events, Actions, and Predicates. Being able to query this metadata and understand what it represents is an essential skill to more effective Extended Event Session management.

Thus far, I have demonstrated how to perform metadata inquiries from both PowerShell and TSQL. I even showed how to map this metadata between script and the GUI screens. As I concluded the metadata discovery via TSQL segment, I demonstrated a very useful script, to generate backup scripts of deployed sessions, using all of the concepts discussed to that point. That script can be found here. In this article, I will share how to do the same thing via PowerShell.

Backstory

Before I go into depth on the script within PowerShell that can be used to backup a deployed session, I want to share a bit of a story.

Many years ago while still in High School, I had a really tough teacher for Calculus. He had a reputation going back to long before I took any of his classes of being a very hard teacher. I enjoyed this teacher quite a bit. My group of friends and I even enjoyed giving him a hard time because it seemed like he could take it as well as dish it out a little.

This teacher had a few philosophies that I didn’t really much care for at the time, but I can sure appreciate now. Truth be told, I gained an appreciation for his teaching philosophies within the first year out of High School. One of his philosophies was to make the High School classes at least on par with what could be expected in College. This made college significantly easier.

Another of his philosophies was to teach core concepts first then to build on those concepts. While teaching the core concepts, he always showed the hard way of doing things first and sometimes an equally hard method as the secondary approach. But there was always an easy way of doing things that would be eventually taught. Sometimes it is essential to a solid foundation to know how to solve a problem the hard way before learning how to do it the easy way.

Correlating that to SQL Server, there are many times that the first approach to do something may be a more difficult approach. As the skills are learned, then maybe an easier solution may present itself, but there is (hopefully there is) a solid foundation there first to help support and enable the learning of an easier method.

Simplified Session Backups

In the aforementioned article, I shared a script that would recreate a deployed session via TSQL. That script was lengthy and required a few tricks to get things to work out just right (e.g. concatenations). Now I will show how to do the same thing in a sickeningly simpler way.

The example that I am about to share requires that SQLPS be loaded and the “demosession” Session be loaded into an object. If a recap of how to do that is needed, I recommend reading this article prior to proceeding.

With the “demosession” Session loaded into an object, I also want to load the name of the session into a parameter to simplify things just a bit.

$sessionname = $sessionobj.Name

I can confirm the value of the $sessionname parameter if I choose. In this case, the value does return as “demosession” and is the correct value needed. With that, now I can run the following script to create a backup of my deployed session:

$sessionobj.ScriptCreate().GetScript() >  C:\Database\XE\SessionBackup\$sessionname.sql

That is the entire script! Just one line! This script could have been even smaller, but I felt it necessary to dump the script out to a SQL file in order to have the script available for future needs – should it be needed. One caveat here is that the directory path must exist or an error will be thrown. To create the directory path, it is as simple as the old DOS commands of old to create that directory.

md C:\Database\XE\SessionBackup

While the script produced is perfectly viable to recreate a deployed session, it is important to note that the Events listed in this script will be in the order of Package then Event sorted alphabetically. Recall from the prior article on Session backups that this is the default script order used by SSMS as well and is not necessarily the same order that the original Session was created.

How did I arrive at this method to create this script? This goes back to repeating some of the processes I have used throughout these articles on PoSH and Extended Events. Starting with a quick exploration into the $sessionobject object.

$sessionobj | gm

This would lead me to seeing the following results:

Seeing ScriptCreate as an option is a great indicator that I can do something with this Session to quickly recreate the session script. If I run that with the session obj, then I get the next clue.

$sessionobj.ScriptCreate()

I highlighted the interesting clue to this. Even though it says ScriptCreate, it apparently needs to have an additional command in order for it to do anything. With that in mind, a quick Get-Methods on ScriptCreate() will produce insight that there are commands that can be utilized. One of those commands happens to be GetScript(). GetScript seems pretty self-explanatory, so I would settle on that and voila the script can be reproduced.

$sessionobj.ScriptCreate() | gm

There it is. I have shown two methods to create backups of deployed sessions. The first being the more complex solution and then this extremely simple solution. Both are valid means to produce the backup scripts and it now just boils down to a choice as to which to use.

This is the latest article in the 60 days of XE series. If you have missed any of the articles, there is a recap for the series. To recap or reread the articles in this series, please visit the table of contents.