< Back
Export-DbaXESession
Post
NAME Export-DbaXESession
SYNOPSIS
Exports Extended Events creation script to a T-SQL file or console.
SYNTAX
Export-DbaXESession [[-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
[[-SqlCredential] <Pscredential>] [[-InputObject] <Microsoft.SqlServer.Management.XEvent.Session[]>] [[-Session]
<System.String[]>] [[-Path] <String>] [[-FilePath] <String>] [[-Encoding] <String>] [-Passthru <Switch>]
[[-BatchSeparator] <String>] [-NoPrefix <Switch>] [-NoClobber <Switch>] [-Append <Switch>] [-EnableException
<Switch>] [<CommonParameters>]
DESCRIPTION
Exports script to create Extended Events Session to sql file or console.
PARAMETERS
-Append [<Switch>]
Append to file. Only required if FilePath is specified
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-BatchSeparator [<String>]
Batch separator for scripting output. Uses the value from configuration Formatting.BatchSeparator by default.
This is normally "GO"
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables
advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own
try/catch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Encoding [<String>]
Specifies the file encoding. The default is UTF8.
Valid values are:
-- ASCII: Uses the encoding for the ASCII (7-bit) character set.
-- BigEndianUnicode: Encodes in UTF-16 format using the big-endian byte order.
-- Byte: Encodes a set of characters into a sequence of bytes.
-- String: Uses the encoding type for a string.
-- Unicode: Encodes in UTF-16 format using the little-endian byte order.
-- UTF7: Encodes in UTF-7 format.
-- UTF8: Encodes in UTF-8 format.
-- Unknown: The encoding type is unknown or invalid. The data can be treated as binary.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-FilePath [<String>]
Specifies the full file path of the output file.
If FilePath is specified and more than one Server is in input then -Append parameter is required to avoid
overwriting data
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<Microsoft.SqlServer.Management.XEvent.Session[]>]
A SQL Management Object - Microsoft.SqlServer.Management.XEvent.Session such as the one returned from
Get-DbaSession
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoClobber [<Switch>]
Do not overwrite file. Only required if FilePath is specified
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoPrefix [<Switch>]
If this switch is used, the scripts will not include prefix information containing creator and datetime.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Passthru [<Switch>]
Output script to console
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<String>]
Specifies the directory where the file or files will be exported.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Session [<System.String[]>]
The Extended Event Session(s) to process. If unspecified, all Extended Event Sessions will be processed. This
is ignored if An input object from Get-DbaSession is specified
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances. This can be a collection and receive pipeline input.
Server version must be SQL Server version 2008 or higher.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
A DbaInstanceParameter representing an array of SQL Server instances or output from Get-DbaSession
OUTPUTS
Creates a new file for each SQL Server Instance
NOTES
Tags: ExtendedEvent, XE, XEvent
Author: Patrick Flynn (@sqllensman)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Export-DbaXESession -SqlInstance sourceserver -Passthru
Exports a script to create all Extended Events Sessions on sourceserver to the console
Will include prefix information containing creator and datetime. and uses the default value for BatchSeparator
value from configuration Formatting.BatchSeparator
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Export-DbaXESession -SqlInstance sourceserver
Exports a script to create all Extended Events Sessions on sourceserver. As no Path was defined - automatically
determines filename based on the Path.DbatoolsExport configuration setting, current time and server name like
Servername-YYYYMMDDhhmmss-sp_configure.sql
Will include prefix information containing creator and datetime. and uses the default value for BatchSeparator
value from configuration Formatting.BatchSeparator
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Export-DbaXESession -SqlInstance sourceserver -FilePath C:\\temp
Exports a script to create all Extended Events Sessions on sourceserver to the directory C:\\temp using the default
name format of Servername-YYYYMMDDhhmmss-sp_configure.sql
Will include prefix information containing creator and datetime. and uses the default value for BatchSeparator
value from configuration Formatting.BatchSeparator
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>$cred = Get-Credential sqladmin
PS C:\\> Export-DbaXESession -SqlInstance sourceserver -SqlCredential $cred -FilePath C:\\temp\\EEvents.sql
-BatchSeparator "" -NoPrefix -NoClobber
Exports a script to create all Extended Events Sessions on sourceserver to the file C:\\temp\\EEvents.sql.
Will exclude prefix information containing creator and datetime and does not include a BatchSeparator
Will not overwrite file if it already exists
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>'Server1', 'Server2' | Export-DbaXESession -FilePath 'C:\\Temp\\EE.sql' -Append
Exports a script to create all Extended Events Sessions for Server1 and Server2 using pipeline.
Writes to a single file using the Append switch
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Get-DbaXESession -SqlInstance Server1, Server2 -Session system_health | Export-DbaXESession -Path 'C:\\Temp'
Exports a script to create the System_Health Extended Events Sessions for Server1 and Server2 using pipeline.
Write to the directory C:\\temp using the default name format of Servername-YYYYMMDDhhmmss-sp_configure.sql
Will include prefix information containing creator and datetime. and uses the default value for BatchSeparator
value from configuration Formatting.BatchSeparator
RELATED LINKS
https://dbatools.io/Export-DbaXESession
SYNOPSIS
Exports Extended Events creation script to a T-SQL file or console.
SYNTAX
Export-DbaXESession [[-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
[[-SqlCredential] <Pscredential>] [[-InputObject] <Microsoft.SqlServer.Management.XEvent.Session[]>] [[-Session]
<System.String[]>] [[-Path] <String>] [[-FilePath] <String>] [[-Encoding] <String>] [-Passthru <Switch>]
[[-BatchSeparator] <String>] [-NoPrefix <Switch>] [-NoClobber <Switch>] [-Append <Switch>] [-EnableException
<Switch>] [<CommonParameters>]
DESCRIPTION
Exports script to create Extended Events Session to sql file or console.
PARAMETERS
-Append [<Switch>]
Append to file. Only required if FilePath is specified
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-BatchSeparator [<String>]
Batch separator for scripting output. Uses the value from configuration Formatting.BatchSeparator by default.
This is normally "GO"
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables
advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own
try/catch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Encoding [<String>]
Specifies the file encoding. The default is UTF8.
Valid values are:
-- ASCII: Uses the encoding for the ASCII (7-bit) character set.
-- BigEndianUnicode: Encodes in UTF-16 format using the big-endian byte order.
-- Byte: Encodes a set of characters into a sequence of bytes.
-- String: Uses the encoding type for a string.
-- Unicode: Encodes in UTF-16 format using the little-endian byte order.
-- UTF7: Encodes in UTF-7 format.
-- UTF8: Encodes in UTF-8 format.
-- Unknown: The encoding type is unknown or invalid. The data can be treated as binary.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-FilePath [<String>]
Specifies the full file path of the output file.
If FilePath is specified and more than one Server is in input then -Append parameter is required to avoid
overwriting data
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<Microsoft.SqlServer.Management.XEvent.Session[]>]
A SQL Management Object - Microsoft.SqlServer.Management.XEvent.Session such as the one returned from
Get-DbaSession
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoClobber [<Switch>]
Do not overwrite file. Only required if FilePath is specified
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoPrefix [<Switch>]
If this switch is used, the scripts will not include prefix information containing creator and datetime.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Passthru [<Switch>]
Output script to console
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<String>]
Specifies the directory where the file or files will be exported.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Session [<System.String[]>]
The Extended Event Session(s) to process. If unspecified, all Extended Event Sessions will be processed. This
is ignored if An input object from Get-DbaSession is specified
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances. This can be a collection and receive pipeline input.
Server version must be SQL Server version 2008 or higher.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
A DbaInstanceParameter representing an array of SQL Server instances or output from Get-DbaSession
OUTPUTS
Creates a new file for each SQL Server Instance
NOTES
Tags: ExtendedEvent, XE, XEvent
Author: Patrick Flynn (@sqllensman)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Export-DbaXESession -SqlInstance sourceserver -Passthru
Exports a script to create all Extended Events Sessions on sourceserver to the console
Will include prefix information containing creator and datetime. and uses the default value for BatchSeparator
value from configuration Formatting.BatchSeparator
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Export-DbaXESession -SqlInstance sourceserver
Exports a script to create all Extended Events Sessions on sourceserver. As no Path was defined - automatically
determines filename based on the Path.DbatoolsExport configuration setting, current time and server name like
Servername-YYYYMMDDhhmmss-sp_configure.sql
Will include prefix information containing creator and datetime. and uses the default value for BatchSeparator
value from configuration Formatting.BatchSeparator
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Export-DbaXESession -SqlInstance sourceserver -FilePath C:\\temp
Exports a script to create all Extended Events Sessions on sourceserver to the directory C:\\temp using the default
name format of Servername-YYYYMMDDhhmmss-sp_configure.sql
Will include prefix information containing creator and datetime. and uses the default value for BatchSeparator
value from configuration Formatting.BatchSeparator
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>$cred = Get-Credential sqladmin
PS C:\\> Export-DbaXESession -SqlInstance sourceserver -SqlCredential $cred -FilePath C:\\temp\\EEvents.sql
-BatchSeparator "" -NoPrefix -NoClobber
Exports a script to create all Extended Events Sessions on sourceserver to the file C:\\temp\\EEvents.sql.
Will exclude prefix information containing creator and datetime and does not include a BatchSeparator
Will not overwrite file if it already exists
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>'Server1', 'Server2' | Export-DbaXESession -FilePath 'C:\\Temp\\EE.sql' -Append
Exports a script to create all Extended Events Sessions for Server1 and Server2 using pipeline.
Writes to a single file using the Append switch
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Get-DbaXESession -SqlInstance Server1, Server2 -Session system_health | Export-DbaXESession -Path 'C:\\Temp'
Exports a script to create the System_Health Extended Events Sessions for Server1 and Server2 using pipeline.
Write to the directory C:\\temp using the default name format of Servername-YYYYMMDDhhmmss-sp_configure.sql
Will include prefix information containing creator and datetime. and uses the default value for BatchSeparator
value from configuration Formatting.BatchSeparator
RELATED LINKS
https://dbatools.io/Export-DbaXESession