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

$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;"
        }
    }

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

1	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
    }

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

$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:\Database\XE\PoshScriptOut\" #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\$yourinstancename\sessions


$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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

# 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:\Database\XE\PoshScriptOut\" #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\$yourinstancename\sessions

$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){