Mass Backup All Sessions

Migrating Extended Event Sessions from one server to another should be a simple task. So simple, one would think there was no need to give it a second thought, right?

Well, I have previously written about this topic, you are welcome to read it here. The article discusses quite a bit about scripting out your XE Sessions. One thing lacking in that article is the ability to script out every session on a server.

If you are still not using Extended Events, I recommend checking out this library of articles that will cover just about all of the basics concerning Extended Events.

New and Improved

What about scripting out all of the sessions in SSMS? Surely there is an easy way to do that, right? Well, you might think that. Let me step through the problem that I have seen in SSMS (and unfortunately it is not consistent).

First, from Object Explorer Details (or F5), let’s try to script a single session.

When scripting a single session from the “Object Explorer Details”, I have several sub-menus that allow me to script the session to a “New Query Editor Window”. Now, let’s see what happens when trying to script multiple sessions.

With several sessions selected, I try yet again to script the sessions and I get an unwanted result. Notice that the “Script Session as” option is grayed out and unusable. However, if I try it again (several times or maybe just once, your mileage may vary and it seems to not be relevant to version of SSMS), I may see something like this.

Tada! Luck was with me and it finally worked that time. So, what should I do to be able to consistently script all of sessions? Well, that comes with an enhancement to the script I presented in the prior article here.

Lets just dive straight into the new script.

SET NOCOUNT ON;

DECLARE
	@statement	   VARCHAR(MAX)
  , @statement1	   VARCHAR(MAX)
  , @statement2	   VARCHAR(MAX)
  , @sessionname   VARCHAR(128)				 --	= 'AlwaysOn_health'						 -- 'system_health'
  , @csessionname  VARCHAR(128)
  , @XeFilePath	   VARCHAR(256)
  , @IsDefaultPath TINYINT
											 /* order of events scripted is different 
default order of creation is different than if scripted from the gui
the gui scripts by alpha order of package then event
while original creation has order by event_id only
*/
  , @eventorder	   VARCHAR(64) = 'original'; --'original' for order based on original script, --'ssms' for order based on ssms guy scripter 

DECLARE xessions CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
	SELECT
				 ses.name
		FROM	 sys.server_event_sessions ses
		WHERE	 ses.name = ISNULL(@sessionname, ses.name)
		ORDER BY ses.name;

OPEN xessions;

FETCH NEXT FROM xessions
INTO
	@csessionname;

DECLARE @ErrorLog VARCHAR(256) = CONVERT(VARCHAR(256), SERVERPROPERTY('ErrorLogFileName'));
DECLARE @ErrorPath VARCHAR(256) = LEFT(@ErrorLog, LEN(@ErrorLog) - CHARINDEX('\', REVERSE(@ErrorLog)) + 1);

WHILE @@FETCH_STATUS = 0
	BEGIN
		IF EXISTS
			(
				SELECT
								   1
					FROM		   sys.server_event_sessions	   ses
						INNER JOIN sys.server_event_session_fields sesf
							ON ses.event_session_id = sesf.event_session_id
					WHERE		   sesf.name = 'filename'
								   AND ses.name = @csessionname
			)
			BEGIN
				IF
					(
						SELECT
										   CHARINDEX('\', CONVERT(VARCHAR(256), sesf.value))
							FROM		   sys.server_event_sessions	   ses
								INNER JOIN sys.server_event_session_fields sesf
									ON ses.event_session_id = sesf.event_session_id
							WHERE		   sesf.name = 'filename'
										   AND ses.name = @csessionname
					) > 0
					BEGIN
						SELECT
										   @XeFilePath
							= LEFT(CONVERT(VARCHAR(256), sesf.value), LEN(CONVERT(VARCHAR(256), sesf.value))
																	  - CHARINDEX(
																					 '\'
																				   , REVERSE(CONVERT(
																										VARCHAR(256)
																									  , sesf.value
																									)
																							)
																				 ) + 1)
							FROM		   sys.server_event_sessions	   ses
								INNER JOIN sys.server_event_session_fields sesf
									ON ses.event_session_id = sesf.event_session_id
							WHERE		   sesf.name = 'filename'
										   AND ses.name = @csessionname;
					END;
				ELSE
					BEGIN
						IF
							(
								SELECT				TOP 1
													LEFT(t2.file_name, LEN(t2.file_name) - CHARINDEX('\', REVERSE(t2.file_name)) + 1) AS XEFilePath
									FROM
													(
														SELECT
																		   REPLACE(CONVERT(NVARCHAR(128), sesf.value), '.xel', '*.xel') AS targetvalue
																		 , ses.name														AS SessionName
															FROM		   sys.server_event_sessions	   ses
																INNER JOIN sys.server_event_session_fields sesf
																	ON ses.event_session_id = sesf.event_session_id
															WHERE		   sesf.name = 'filename'
																		   AND ses.name = @csessionname
													)																	cte1
										CROSS APPLY sys.fn_xe_file_target_read_file(cte1.targetvalue, NULL, NULL, NULL) t2
							) IS NOT NULL
							BEGIN
								SELECT
									@XeFilePath
									=
									(
										SELECT				TOP 1
															LEFT(t2.file_name, LEN(t2.file_name)
																			   - CHARINDEX('\', REVERSE(t2.file_name)) + 1) AS XEFilePath
											FROM
															(
																SELECT
																				   REPLACE(CONVERT(NVARCHAR(128), sesf.value), '.xel', '*.xel') AS targetvalue
																				 , ses.name														AS SessionName
																	FROM		   sys.server_event_sessions	   ses
																		INNER JOIN sys.server_event_session_fields sesf
																			ON ses.event_session_id = sesf.event_session_id
																	WHERE		   sesf.name = 'filename'
																				   AND ses.name = @csessionname
															)								 cte1
												CROSS APPLY sys.fn_xe_file_target_read_file(
																							   cte1.targetvalue
																							 , NULL
																							 , NULL
																							 , NULL
																						   ) t2
									);
							END;
						ELSE
							BEGIN
								SELECT
									@XeFilePath = @ErrorPath;
								SELECT
									@XeFilePath
								  , @ErrorPath;
							END;

					END;
			END;

		IF @ErrorPath = @XeFilePath
			BEGIN
				SET @IsDefaultPath = 1;
			END;
		ELSE
			BEGIN
				SET @IsDefaultPath = 0;
			END;
		SELECT
				  @statement
			= 'USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
				FROM sys.server_event_sessions
				WHERE name = ' + ses.name + ' )
	DROP EVENT SESSION ' + ses.name + ' 
    ON SERVER;
GO
'
			FROM  sys.server_event_sessions ses
			WHERE ses.name = @csessionname;

		IF @IsDefaultPath = 1
			BEGIN
				SET @statement = @statement + '';
			END;
		ELSE
			BEGIN
				SET @statement = @statement + '
EXECUTE xp_create_subdir '''	 + @XeFilePath + ''';
GO
'				;
			END;
		SELECT
				  @statement = @statement + '
CREATE EVENT SESSION ['	 + ses.name + '] ON SERVER
	'
			FROM  sys.server_event_sessions ses
			WHERE ses.name = @csessionname;

		WITH presel AS
			(SELECT
								ses.name											 AS SessionName
							  , sese.event_id
							  , sese.package
							  , 'ADD EVENT ' + sese.package + '.' + sese.name + ' (' AS EventName
							  , CASE
									WHEN ISNULL(sese.predicate, '') = ''
										 THEN ')'
									ELSE 'WHERE ' + sese.predicate + ' )'
								END													 AS SQLPredicate
							  , CASE
									WHEN ISNULL(sesf.EventSetOps, '') = ''
										 THEN ''
									ELSE 'SET ' + sesf.EventSetOps + ''
								END													 AS SETOperation
				 FROM			sys.server_event_sessions		ses
					 INNER JOIN sys.server_event_session_events sese
						 ON ses.event_session_id = sese.event_session_id
					 LEFT OUTER JOIN
								(
									SELECT	 DISTINCT
											 ST2.event_session_id
										   , ST2.object_id
										   , SUBSTRING(
												 (
													 SELECT
																		', ' + ST1.name + '=' + '(' + CONVERT(VARCHAR(60), ST1.value) + ')' AS [text()]
														 FROM			sys.server_event_session_fields ST1
															 INNER JOIN sys.server_event_session_events sese
																 ON sese.event_session_id = ST1.event_session_id
																	AND sese.event_id = ST1.object_id
														 WHERE			ST1.event_session_id = ST2.event_session_id
																		AND ST1.object_id = ST2.object_id
														 ORDER BY		ST1.object_id
													 FOR XML PATH('')
												 )
											   , 3
											   , 8000
													  ) [EventSetOps]
										FROM sys.server_event_session_fields ST2
								)								sesf
						 ON sese.event_session_id = sesf.event_session_id
							AND sese.event_id = sesf.object_id
				 WHERE			ses.name = @csessionname)
		   , actpresel AS
			(SELECT
								ses.name					   AS SessionName
							  , sesa.event_session_id
							  , sesa.event_id
							  , sesa.package + '.' + sesa.name AS ActPack
				 FROM			sys.server_event_session_events	 sese
					 INNER JOIN sys.server_event_session_actions sesa
						 ON sese.event_session_id = sesa.event_session_id
							AND sese.event_id = sesa.event_id
					 INNER JOIN sys.server_event_sessions		 ses
						 ON sesa.event_session_id = ses.event_session_id
				 WHERE			ses.name = @csessionname)
		   , pstuff AS
			(SELECT
						  p.SessionName
						, p.event_id
						, 'ACTION ( 
				' +		STUFF((
								  SELECT
											', ' + pin.ActPack + '
				'
									  FROM	actpresel pin
									  WHERE pin.SessionName = p.SessionName
											AND pin.event_id = p.event_id
								  FOR XML PATH(''), TYPE
							  ).value('.', 'varchar(max)')
							, 1
							, 2
							, ''
							 ) + '
			)'			AS ActionPack
				 FROM	  actpresel p
				 GROUP BY p.SessionName
						, p.event_id)
		SELECT
			@statement1 = @statement + STUFF((
												 SELECT
																		 ', ' + CAST(p.EventName + '
		'														 + p.SETOperation + '
		'														 + CASE
																	   WHEN ISNULL(ps.ActionPack, '') = ''
																			THEN ''
																	   ELSE ps.ActionPack
																   END + '
		'														 + p.SQLPredicate + '
'													 AS VARCHAR(MAX)) AS EventStatement
													 FROM				 presel p
														 LEFT OUTER JOIN pstuff ps
															 ON p.SessionName = ps.SessionName
																AND p.event_id = ps.event_id
													 ORDER BY			 p.event_id
												 --ORDER BY p.package, p.EventName 
												 FOR XML PATH(''), TYPE
											 ).value('.', 'varchar(max)')
										   , 1
										   , 2
										   , ''
											)
		  , @statement2 = @statement + STUFF((
												 SELECT
																		 ', ' + CAST(p.EventName + '
		'														 + p.SETOperation + '
		'														 + CASE
																	   WHEN ISNULL(ps.ActionPack, '') = ''
																			THEN ''
																	   ELSE ps.ActionPack
																   END + '
		'														 + p.SQLPredicate + '
'													 AS VARCHAR(MAX)) AS EventStatement
													 FROM				 presel p
														 LEFT OUTER JOIN pstuff ps
															 ON p.SessionName = ps.SessionName
																AND p.event_id = ps.event_id
													 ORDER BY			 p.package
																	   , p.EventName
												 FOR XML PATH(''), TYPE
											 ).value('.', 'varchar(max)')
										   , 1
										   , 2
										   , ''
											);

		IF @eventorder = 'original'
			BEGIN
				SET @statement = @statement1;
			END;
		IF @eventorder = 'ssms'
			BEGIN
				SET @statement = @statement2;
			END;

		SELECT
			@statement
			= @statement
			  + STUFF(
			(
				SELECT
								   ', ' + '
ADD TARGET '										+ sest.package + '.' + sest.name
								   + CASE
										 WHEN ISNULL(ca.setop, '') = ''
											  THEN ' 
'
										 ELSE '
			(SET ' +								ca.setop + ' )
'
									 END
					FROM		   sys.server_event_sessions		ses
						INNER JOIN sys.server_event_session_targets sest
							ON ses.event_session_id = sest.event_session_id
						CROSS APPLY
								   (
									   SELECT
										   STUFF(
			(
				SELECT
								   ', ' + CAST(sesf.name AS VARCHAR(128)) + ' = '
								   + CASE
										 WHEN ISNUMERIC(CAST(sesf.value AS VARCHAR(128))) = 1
											  THEN '( ' + CAST(sesf.value AS VARCHAR(128)) + ' )
							'
										 ELSE 'N''' + CAST(sesf.value AS VARCHAR(128)) + '''
						'
									 END AS setop
					FROM		   sys.server_event_session_fields	sesf
						INNER JOIN sys.server_event_sessions		sesi
							ON sesi.event_session_id = sesf.event_session_id
						INNER JOIN sys.server_event_session_targets sesti
							ON sesf.object_id = sesti.target_id
							   AND sesi.event_session_id = sesti.event_session_id
					WHERE		   sest.target_id = sesti.target_id
								   AND sest.event_session_id = sesti.event_session_id
				FOR XML PATH(''), TYPE
			).value('.', 'varchar(max)')
		  , 1
		  , 2
		  , ''
												)
								   )								AS ca(setop)
					WHERE		   ses.name = @csessionname
				FOR XML PATH(''), TYPE
			).value('.', 'varchar(max)')
		  , 1
		  , 2
		  , ''
					 );

		SELECT
				  @statement
			= @statement
			  + CAST('WITH ( MAX_MEMORY = ' + CAST(ses.max_memory AS VARCHAR(20)) + ' KB
		,EVENT_RETENTION_MODE = '		   + ses.event_retention_mode_desc + '
		, MAX_DISPATCH_LATENCY = '		   + CAST(ses.max_dispatch_latency / 1000 AS VARCHAR(20))
					 + ' SECONDS
		, MAX_EVENT_SIZE = '			   + CAST(ses.max_event_size AS VARCHAR(20))
					 + ' KB
		, MEMORY_PARTITION_MODE = '		   + ses.memory_partition_mode_desc + '
		, TRACK_CAUSALITY = '			   + CASE ses.track_causality
												 WHEN 1
													  THEN 'ON'
												 ELSE 'OFF'
											 END + '
		, STARTUP_STATE = '				   + CASE ses.startup_state
												 WHEN 1
													  THEN 'ON'
												 ELSE 'OFF'
											 END + '
	);'		AS VARCHAR(1024))
			FROM  sys.server_event_sessions ses
			WHERE ses.name = @csessionname;

		PRINT @statement;

		FETCH NEXT FROM xessions
		INTO
			@csessionname;
	END;

CLOSE xessions;
DEALLOCATE xessions;

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

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

SET NOCOUNT ON;

DECLARE

@statement VARCHAR(MAX)

, @statement1 VARCHAR(MAX)

, @statement2 VARCHAR(MAX)

, @sessionname VARCHAR(128) -- = 'AlwaysOn_health' -- 'system_health'

, @csessionname VARCHAR(128)

, @XeFilePath VARCHAR(256)

, @IsDefaultPath TINYINT

/* order of events scripted is different

default order of creation is different than if scripted from the gui

the gui scripts by alpha order of package then event

while original creation has order by event_id only

, @eventorder VARCHAR(64) = 'original'; --'original' for order based on original script, --'ssms' for order based on ssms guy scripter

DECLARE xessions CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR

SELECT

ses.name

FROM sys.server_event_sessions ses

WHERE ses.name = ISNULL(@sessionname, ses.name)

ORDER BY ses.name;

OPEN xessions;

FETCH NEXT FROM xessions

INTO

@csessionname;

DECLARE @ErrorLog VARCHAR(256) = CONVERT(VARCHAR(256), SERVERPROPERTY('ErrorLogFileName'));

DECLARE @ErrorPath VARCHAR(256) = LEFT(@ErrorLog, LEN(@ErrorLog) - CHARINDEX('\', REVERSE(@ErrorLog)) + 1);

WHILE @@FETCH_STATUS = 0

BEGIN

IF EXISTS

(

SELECT

FROM sys.server_event_sessions ses

INNER JOIN sys.server_event_session_fields sesf

ON ses.event_session_id = sesf.event_session_id

WHERE sesf.name = 'filename'

AND ses.name = @csessionname

)

BEGIN

(

SELECT

CHARINDEX('\', CONVERT(VARCHAR(256), sesf.value))

FROM sys.server_event_sessions ses

INNER JOIN sys.server_event_session_fields sesf

ON ses.event_session_id = sesf.event_session_id

WHERE sesf.name = 'filename'

AND ses.name = @csessionname

) > 0

BEGIN

SELECT

@XeFilePath

= LEFT(CONVERT(VARCHAR(256), sesf.value), LEN(CONVERT(VARCHAR(256), sesf.value))

- CHARINDEX(

'\'

, REVERSE(CONVERT(

VARCHAR(256)

, sesf.value

)

) + 1)

FROM sys.server_event_sessions ses

INNER JOIN sys.server_event_session_fields sesf

ON ses.event_session_id = sesf.event_session_id

WHERE sesf.name = 'filename'

AND ses.name = @csessionname;

END;

ELSE

BEGIN

(

SELECT<