Backup Deployed Sessions

Having just completed several segments on the metadata for deployed sessions, it is time to bring all of that information together and see how it can be used – by showing how to backup deployed sessions.

There is a wealth of information within Extended Events. Throughout this series, I have been working on exposing that wealth of information. A good bit of this information happens to be the metadata for Extended Events, and another bit of this information is tied craneto the metadata for deployed Sessions. To dive into what I have done so far with this plenitude of information, you can review the series here.

What is the point in covering all of this information? I like to call it building blocks. Take a concept little by little and build upon it, while working toward a more comprehensive understanding as well as to hopefully be able to use it for a bigger product.

Having just completed several segments on the metadata for deployed sessions, it is time to bring all of that information together and see how it can be used.

Bringing it Together

Having a solid foundation and understanding of what the metadata is and where it is really helps in creating scripts that can be useful in the day to day management of deployed Extended Event Sessions. For me, the ability to recreate a deployed session, without the use of the GUI is pretty essential. Contributing to the need to be able to do this via script and not a GUI is that (without a third party product) there is no GUI in SQL Server 2008.

To demonstrate how to create a script to serve as a backup of a deployed session, I will first return to the “demosession” script that I have used throughout this series.

And if I color-code this script as I have done previously:


In this image, I have separated out the major components by color. The segments that pertain to the session creation and the settings for that session are in grey. Then the target information is in red and so on. I added my notes (to help indicate where the components can be found in metadata) and colored them purple. The wrinkle here is in the SET operations since that is a component that crosses boundaries of the core concepts.

Taking this breakdown, and a serious desire to be able to recreate any session that has been deployed (without the GOOEY), and I have been able to create the following script that will do just that – backup deployed sessions.

I have tested this script back through SQL Server 2008 and up through SQL Server 2014. The script has been tested against complex sessions as well as simple sessions (e.g. the “demosession” session I have been using throughout the series).

While testing, I did run into an interesting anomaly so I had to add a little bit of awkwardness to the script. When comparing the original script for “system_health” (that can be found in the u_tables.sql script in the Install folder), to what was produced while scripting sessions from the GUI, I noticed that my script was recreating the session in the same fashion that the original script was doing it. But this did not match what the GUI did.

When scripting from SSMS (the GUI), the events in the session are ordered by Package and then by Event name. My script, originally, was ordering the events by Event_id which is the same as can be found in the u_tables.sql script. Due to this, I added a parameter to allow for the different sort methods. This also underscores that when a session is deployed, the order of Events in that first script is the order the Events will be created within the metadata.

I decided to output the results of the script via a print statement. This permits me a chance to see the entire script and review it. Then, I can copy to a new window and execute or I can save the script.

With this script, I can quickly do what is an essential function of a DBA, I can perform targeted backups of my deployed sessions. Another way of viewing it is that I can reverse engineer (without the GOOEY) any user definable session that is deployed to the server.

This is just one of many articles in this series. In this article, I demonstrated how to backup a deployed session and create a script for recovery purposes of a targeted session. To find more useful information about extended events, you can get caught up here.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.