PowerShell to Backup XE Session

Quite some time ago, I shared a few articles that peeled back the top layer of how to use PowerShell (PoSh) with Extended Events (XEvents). Among those articles, I showed how to retrieve the metadata, predicates and actions, and targets (to mention a few). Those are prime examples of articles showing some of the basics, which means there is plenty of room for some deeper dive articles involving both PoSh and XEvents. One topic that can help us bridge to the deeper end of the XEvents pool is how to generate scripts for our XEvent Sessions.

In this article, I will venture to show how to generate good backup scripts of our sessions using PoSh. That said, there are some caveats to using PoSh to generate these scripts and I will share those as well.

Wading to the Deeper End!

PoSh provides many methods and classes that allow us to more easily do various things. One of my favorite uses for PoSh is to automate tasks across the enterprise database server footprint. Nothing like creating a script that can effectively hit all of the servers one manages in one fell swoop to help minimize effort and time spent touching all of the servers. Afterall, a prime directive of the effective DBA is to be lazy.

So, when I look at creating a script in PoSh, I also look at how can I make this script function for a single server just the same as it would work for hundreds of servers. That will be one of the “nice to have” add-ons that you will see in this script. But, how did we get there in the first place?

A critical component of using PoSh to generate the scripts for each of the XEvent Sessions on the server is the GetScript(). GetScript() can be called for many different objects within PoSh. Another critical component is the XEvent SMO classes that were added as of SQL Server 2012. Take a mental note of that because it will come into play in a bit.

In addition to these critical components, I used some of the stuff that I mentioned in the previous articles as well as my article on XEvent management with PoSh. As I have said many times, these articles are building blocks and do rely upon many fundamentals divulged in previous articles.

Getting into the Script

Given this is a script that interacts with SQL Server, we need to ensure the SqlServer module is installed and loaded. It if is already installed, that is great. During my testing, I found that one of my servers, I ran this locally on, did not have it installed. With that discovery, I figured it is just easier to implement a check. If the module is not installed, then install it.

$modulecheck = Get-InstalledModule -Name SqlServer | Select Name
IF (!$modulecheck){
Install-Module SqlServer -Force
}
 
Import-Module SqlServer

The next major component is how I fetch my list of servers. I prefer to have all of my enterprise SQL Servers listed within SQL Server. I keep a table with all of the servers and designate various attributes for the Servers (e.g. prod or dev, business unit, decommissioned etc).

IF($svrname){
    $SQLServerList = [ordered]@{
        ServerName = $svrname}
    }
ELSE {
    IF($TypeName){
        $SQLServerList = Invoke-Sqlcmd -serverinstance $SQLRepository -database "DBA" -query "
    SELECT DISTINCT
   ServerName
FROM Mgmt.ServerList   sl
INNER JOIN [Mgmt].[ServerType] st
ON sl.ServerTypeID = st.TypeID
WHERE st.TypeName = '$TypeName';"
        }
    ELSE{
        $SQLServerList = Invoke-Sqlcmd -serverinstance $SQLRepository -database "DBA" -query "
    SELECT DISTINCT
   ServerName
FROM Mgmt.ServerList   sl
INNER JOIN [Mgmt].[ServerType] st
ON sl.ServerTypeID = st.TypeID
WHERE st.TypeID <> 7;"
        }
    }

Once, I know which servers need to be queried, I need to make sure I set my PoSh location properly.

SET-Location SQLServer:XEvent$SvrName

With this script, I also have the ability to script out a specific XEvent session or to script every XEvent session deployed on the server. When scripting all sessions on the server, I opted to put all of the sessions into a single output script file. Since, this script can be run against multiple servers, I name each output according to the Server Name and the Instance on that server followed by the XEvent Session name (or all_sessions in the event all sessions are being returned).

IF($XESession){
   $OutputFile = $DestinationFolder + $SvrName + "_" + $yourinstancename + "_" + $XESession + ".sql"
   $session = dir | Where-Object {$_.name -eq $XESession}
   $sessionlist = $session
       }
ELSE {
    $OutputFile = $DestinationFolder + $SvrName + "_" + $yourinstancename + "_all_xesessions.sql"
    $sessionlist = dir #| Select name
    }

Since I prefer to use a custom event_file path, I also wanted to ensure my script would produce a means to ensure the custom file path is created. I needed to validate that this only gets added to those sessions that had an event_file added to it.

$filetarget = ''
    $filetarget = $session.Targets.TargetFields | Where-Object {$_.name -eq 'filename'} | Select Value
    IF ($filetarget -and $filetarget.Value -match ":\"){
     $FileDir = ''
        $FileDir = Split-Path $filetarget.Value -Parent

There are a few more nuggets throughout that help bring this whole thing together. This is what the completed script looks like.

# does not work against 2008R2. The XE smo classes are not backwards compatible to 2008r2
$modulecheck = Get-InstalledModule -Name SqlServer | Select Name
IF (!$modulecheck){
Install-Module SqlServer -Force
}
 
Import-Module SqlServer

$DestinationFolder = "C:DatabaseXEPoshScriptOut" #make sure to end the path with 
$XESession = "" #"WaitsExt"#= "AlwaysOn_health"
$SvrName = "" #$env:computername "yourservername", empty string fetches all servers
$InstanceName = "" # "default" empty string for all instances on server or as a short-cut
$SQLRepository = "YourServerListDBServer" # enter the name of the SQL Server where your database server list is maintained
$TypeName = $null #e.g. the environment server type such as Prod, Dev, Etc.

IF($svrname){
    $SQLServerList = [ordered]@{
        ServerName = $svrname}
    }
ELSE {
    IF($TypeName){
        $SQLServerList = Invoke-Sqlcmd -serverinstance $SQLRepository -database "DBA" -query "
    SELECT DISTINCT
   ServerName
FROM Mgmt.ServerList   sl
INNER JOIN [Mgmt].[ServerType] st
ON sl.ServerTypeID = st.TypeID
WHERE st.TypeName = '$TypeName';"
        }
    ELSE{
        $SQLServerList = Invoke-Sqlcmd -serverinstance $SQLRepository -database "DBA" -query "
    SELECT DISTINCT
   ServerName
FROM Mgmt.ServerList   sl
INNER JOIN [Mgmt].[ServerType] st
ON sl.ServerTypeID = st.TypeID
WHERE st.TypeID <> 7;"
        }
    }
ForEach  ($server in  $SQLServerList){ 
  $SvrName = $server.ServerName
SET-Location SQLServer:XEvent$SvrName
IF($InstanceName){
    $yourinstancelist = [ordered]@{
        DisplayName = $InstanceName}
    }
ELSE {
$yourinstancelist = get-childitem | Select DisplayName
}
ForEach  ($yourinstancename in $yourinstancelist.DisplayName){
cd XEvent$SvrName$yourinstancenamesessions

$scriptout = ""
If(!(test-path $DestinationFolder))
{
      New-Item -ItemType Directory -Force -Path $DestinationFolder
}
IF($XESession){
   $OutputFile = $DestinationFolder + $SvrName + "_" + $yourinstancename + "_" + $XESession + ".sql"
   $session = dir | Where-Object {$_.name -eq $XESession}
   $sessionlist = $session
       }
ELSE {
    $OutputFile = $DestinationFolder + $SvrName + "_" + $yourinstancename + "_all_xesessions.sql"
    $sessionlist = dir #| Select name
    }
ForEach  ($session in $sessionlist){
    $sessname = $session.name
    $filetarget = ''
    $filetarget = $session.Targets.TargetFields | Where-Object {$_.name -eq 'filename'} | Select Value
    IF ($filetarget -and $filetarget.Value -match ":\"){
     $FileDir = ''
        $FileDir = Split-Path $filetarget.Value -Parent
$sessionscript = $session.ScriptCreate()
$scriptout += 
"USE [master];
GO
-- Create the Event Session
IF EXISTS ( SELECT *
        FROM sys.server_event_sessions
        WHERE name = N'" + $sessname +"' )
    DROP EVENT SESSION [" + $sessname + "]
    ON SERVER;
GO
EXECUTE xp_create_subdir '" + $FileDir + "';
GO
" + 
$sessionscript.GetScript() + ";
"
        #}
    }
    ELSE {
$sessionscript = $session.ScriptCreate()
$scriptout += 
"USE [master];
GO
-- Create the Event Session
IF EXISTS ( SELECT *
        FROM sys.server_event_sessions
        WHERE name = N'" + $sessname +"' )
    DROP EVENT SESSION [" + $sessname + "]
    ON SERVER;
GO
" + 
$sessionscript.GetScript() + ";
"
    }
}
$scriptout | Out-File $OutputFile -Force
}
} #close foreach loop of serverlist

If you follow along on that script, you will see that I concatenate the sessions together and then force overwrite the output file. This ensures that I am not appending to an existing file and also ensures that I have the most current version of the XEvent session script. I configure this to output to the local server from where the script is executed.

Caveats

Using PoSh to generate scripts is extremely handy especially when working across numerous servers. That said, there are certain problems for which one must be aware. First is that the SMO classes for PoSh were introduced as of SQL Server 2012. These objects are not backwards compatible.

The second issue I have may be less of a concern to you than me, but it goes well with concerns I mentioned in the article I wrote about how to do this same task in TSQL. The script generated via PoSh adds the events in alphabetical order. I have also seen on some cases where the predicate is not in the exact order that was originally created. Predicate order is essential! Event order may not be an essential thing for anything other than Source Control or for the demanding OCD DBA. Your mileage may vary.

Final Thoughts

Bringing PoSh to the world of XEvents can open up your eyes to a world of better automation and DBA efficiency as you manage your enterprise. PoSh also can help us dive deeper into the world of XEvents as we will see in some upcoming articles.

Working with Extended Events will help you become a better DBA. Working with PoSh can also help you in many various tasks to become a better DBA. Combine the two and you just might have a super weapon.

Interested in exploring the vast world of Extended Events? Check these out! The library of articles is pretty large and continues to grow.

Syspolicy Phantom Health Records April 6, 2018
Automating like an Enterprise DBA January 8, 2019
PowerShell ISE Crashes November 19, 2018
T-SQL Tuesday #108: New Horizons Beyond SQL Server November 13, 2018
Easy Audit Index Changes December 27, 2019