{"id":7902,"date":"2016-05-12T14:30:36","date_gmt":"2016-05-12T12:30:36","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/"},"modified":"2016-05-12T14:30:36","modified_gmt":"2016-05-12T12:30:36","slug":"sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/","title":{"rendered":"SQL Server 2016: Extended Events are more powerful as Trace Flag for Backups and Restores"},"content":{"rendered":"<p>Before SQL Server 2016, we use Trace Flag to display information about backup and restore operations like the 3004, 3014, 3213 or 3604.<br \/>\nHere a link to the MSDN with a great <a title=\"MSDN link for Trace Flag List\" href=\"http:\/\/social.technet.microsoft.com\/wiki\/contents\/articles\/13105.trace-flags-in-sql-server.aspx\" target=\"_blank\">SQL Server trace flag list<\/a><\/p>\n<p>SQL Server 2016 provides 2 new Extended Events:<\/p>\n<ul>\n<li>backup_restore_progress_trace<\/li>\n<li>database_backup_restore_throughput<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup00.png\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-8586 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup00.png\" alt=\"SQL2016_backup00\" width=\"300\" height=\"156\" \/><\/a><br \/>\nbackup_restore_progress_trace will give detailed information during the backup process and restore operation<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup01.png\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-8587 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup01.png\" alt=\"SQL2016_backup01\" width=\"300\" height=\"158\" \/><\/a><br \/>\ndatabase_backup_restore_throughput will provide different throughput about backup\/restore process<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup02.png\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-8588 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup02.png\" alt=\"SQL2016_backup02\" width=\"300\" height=\"222\" \/><\/a><\/p>\n<h3>Create an Extended Event for Backup &amp; Restore operations<\/h3>\n<p>I create an Event Session script called \u201cBackup Restore Information\u201d with both events, I don\u2019t start my event session at creation:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">CREATE EVENT SESSION [Backup Restore Information] ON SERVER \nADD EVENT sqlserver.backup_restore_progress_trace(\n    ACTION(sqlserver.client_hostname,sqlserver.database_name)),\nADD EVENT sqlserver.databases_backup_restore_throughput(\n    ACTION(sqlserver.client_hostname,sqlserver.database_name))\nADD TARGET package0.event_file(SET filename=N'C:\\ExtendedEvent\\Backup_Restore_information.xel')\nWITH (STARTUP_STATE=OFF)\nGO\n<\/pre>\n<p>All information are stored in a file called \u201cBackup_Restore_information.xel\u201d. I start now my Event session:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">ALTER EVENT SESSION [Backup Restore Information] ON SERVER STATE = START\n<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup03.png\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-8589 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup03.png\" alt=\"SQL2016_backup03\" width=\"300\" height=\"108\" \/><\/a><br \/>\nTo have a good test, I downloaded the AdventuredWorks sample for SQL Server 2016 <a title=\"AdventureWorks 2016\" href=\"https:\/\/www.microsoft.com\/en-us\/download\/confirmation.aspx?id=49502\" target=\"_blank\">here<\/a><\/p>\n<h3>Restore a database<\/h3>\n<p>The first test is to restore the database [AdventureworksDW2016CTP3]<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup04.png\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-8590 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup04.png\" alt=\"SQL2016_backup04\" width=\"300\" height=\"134\" \/><\/a><br \/>\nIf I display the Extended Events, I have 1553 Events for a restore<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup05.png\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-8591 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup05.png\" alt=\"SQL2016_backup05\" width=\"300\" height=\"181\" \/><\/a><br \/>\nI add the column trace_message to have more information for the event backup_restore_progress_trace<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup06.png\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-8592 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup06.png\" alt=\"SQL2016_backup06\" width=\"300\" height=\"159\" \/><\/a><br \/>\nIf I add a filter on the column trace_message to avoid \u201cNull\u201d, I have 59 events<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup07.png\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-8593 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup07.png\" alt=\"SQL2016_backup07\" width=\"300\" height=\"124\" \/><\/a><\/p>\n<h3>Backup a database<\/h3>\n<p>Like my restore, I will do the same process with a backup operation:<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup08.png\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-8594 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup08.png\" alt=\"SQL2016_backup08\" width=\"300\" height=\"108\" \/><\/a><br \/>\nIf I look to the Extended Event file, I see 115 events including the 59 events for the restore, so I have 56 events for the backup with a lot of information.<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup09.png\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-8595 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup09.png\" alt=\"SQL2016_backup09\" width=\"300\" height=\"127\" \/><\/a><\/p>\n<p>I will not analyze all information but as example, you can see that the database has an S lock during read operation for the backup. You see also the checkpoint with the time when it occurred, etc.<\/p>\n<h3>Read the extended events file<\/h3>\n<p>To read the file, I use the function<a title=\"sys.fn_xe_file_target_read_file\" href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/cc280743.aspxhttp:\/\/\" target=\"_blank\"> sys.fn_xe_file_target_read_file<\/a><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SELECT \n    n.value('(@name)[1]', 'varchar(50)') AS event_name,\n      DATEADD(hh, \n            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), \n            n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],\n    n.value('(data[@name=\"trace_message\"]\/value)[1]', 'varchar(max)') as trace_message\nFROM \n(SELECT\n    CAST(event_data AS XML) AS event_data\n FROM sys.fn_xe_file_target_read_file('C:\\ExtendedEvent\\Backup_Restore_information_0_131069931247850000.xel',Null, null, null)\n \n) as tab\nCROSS APPLY event_data.nodes('event') as q(n)\nwhere n.value('(data[@name=\"trace_message\"]\/value)[1]', 'varchar(max)') IS NOT NULL\n<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup10.png\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-8596 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup10.png\" alt=\"SQL2016_backup10\" width=\"300\" height=\"160\" \/><\/a><\/p>\n<p>To see the time in millisecond, I add an operation to do this with the timestamp:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">COALESCE(DATEDIFF(MILLISECOND, n.value('(@timestamp)[1]', 'datetime2') , LEAD(n.value('(@timestamp)[1]', 'datetime2'),1) OVER (ORDER BY n.value(N'(event\/action[@name=\"event_sequence\"])[1]', N'int'))) ,0) as Duration\n<\/pre>\n<p>I also add the operation type (backup=0 and restore=1):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">n.value('(data[@name=\"operation_type\"]\/value)[1]', 'int') AS operation_type\n<\/pre>\n<p>And finally, I obtain this query to filter my event file:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SELECT \n    n.value('(@name)[1]', 'varchar(50)') AS event_name,\n\t n.value('(data[@name=\"operation_type\"]\/value)[1]', 'int') AS operation_type,\n      DATEADD(hh, \n            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), \n            n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],\n    n.value('(data[@name=\"trace_message\"]\/value)[1]', 'varchar(max)') as trace_message,\n\tCOALESCE(DATEDIFF(MILLISECOND, n.value('(@timestamp)[1]', 'datetime2') , LEAD(n.value('(@timestamp)[1]', 'datetime2'),1) OVER (ORDER BY n.value(N'(event\/action[@name=\"event_sequence\"])[1]', N'int'))\n\t) ,0) as Duration\nFROM \n(SELECT\n    CAST(event_data AS XML) AS event_data\n FROM sys.fn_xe_file_target_read_file('C:\\ExtendedEvent\\Backup_Restore_information_0_131069931247850000.xel',Null, null, null)\n \n) as tab\nCROSS APPLY event_data.nodes('event') as q(n)\nwhere n.value('(data[@name=\"trace_message\"]\/value)[1]', 'varchar(max)') IS NOT NULL\n<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup11.png\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-8597 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup11.png\" alt=\"SQL2016_backup11\" width=\"300\" height=\"166\" \/><\/a><\/p>\n<p>Be just careful with the result like my test. The \u201cRESTORE DATABASE finished\u201d has normally a value of 0. This little error comes from the LEAD command and the fact that I have a backup 22 minutes after\u2026<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Before SQL Server 2016, we use Trace Flag to display information about backup and restore operations like the 3004, 3014, 3213 or 3604. Here a link to the MSDN with a great SQL Server trace flag list SQL Server 2016 provides 2 new Extended Events: backup_restore_progress_trace database_backup_restore_throughput backup_restore_progress_trace will give detailed information during the backup [&hellip;]<\/p>\n","protected":false},"author":15,"featured_media":7915,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[202,49,51,566],"type_dbi":[],"class_list":["post-7902","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","tag-backup","tag-microsoft","tag-sql-server","tag-sql-server-2016"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Server 2016: Extended Events are more powerful as Trace Flag for Backups and Restores - dbi Blog<\/title>\n<meta name=\"description\" content=\"Before SQL Server 2016, we use Trace Flag to display information about backup and restore operations like the 3004, 3014, 3213 or 3604. Now, SQL Server 2016 provides 2 new Extended Events: backup_restore_progress_trace &amp; database_backup_restore_throughput\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2016: Extended Events are more powerful as Trace Flag for Backups and Restores\" \/>\n<meta property=\"og:description\" content=\"Before SQL Server 2016, we use Trace Flag to display information about backup and restore operations like the 3004, 3014, 3213 or 3604. Now, SQL Server 2016 provides 2 new Extended Events: backup_restore_progress_trace &amp; database_backup_restore_throughput\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-05-12T12:30:36+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup00-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"878\" \/>\n\t<meta property=\"og:image:height\" content=\"456\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"St\u00e9phane Haby\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"St\u00e9phane Haby\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/\"},\"author\":{\"name\":\"St\u00e9phane Haby\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\"},\"headline\":\"SQL Server 2016: Extended Events are more powerful as Trace Flag for Backups and Restores\",\"datePublished\":\"2016-05-12T12:30:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/\"},\"wordCount\":382,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup00-1.png\",\"keywords\":[\"Backup\",\"Microsoft\",\"SQL Server\",\"SQL Server 2016\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/\",\"name\":\"SQL Server 2016: Extended Events are more powerful as Trace Flag for Backups and Restores - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup00-1.png\",\"datePublished\":\"2016-05-12T12:30:36+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\"},\"description\":\"Before SQL Server 2016, we use Trace Flag to display information about backup and restore operations like the 3004, 3014, 3213 or 3604. Now, SQL Server 2016 provides 2 new Extended Events: backup_restore_progress_trace & database_backup_restore_throughput\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup00-1.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup00-1.png\",\"width\":878,\"height\":456},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2016: Extended Events are more powerful as Trace Flag for Backups and Restores\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\",\"name\":\"St\u00e9phane Haby\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"caption\":\"St\u00e9phane Haby\"},\"description\":\"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals &amp; Pharmaceuticals, Banking \/ Financial Services, and many other industries.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/stephane-haby\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server 2016: Extended Events are more powerful as Trace Flag for Backups and Restores - dbi Blog","description":"Before SQL Server 2016, we use Trace Flag to display information about backup and restore operations like the 3004, 3014, 3213 or 3604. Now, SQL Server 2016 provides 2 new Extended Events: backup_restore_progress_trace & database_backup_restore_throughput","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2016: Extended Events are more powerful as Trace Flag for Backups and Restores","og_description":"Before SQL Server 2016, we use Trace Flag to display information about backup and restore operations like the 3004, 3014, 3213 or 3604. Now, SQL Server 2016 provides 2 new Extended Events: backup_restore_progress_trace & database_backup_restore_throughput","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/","og_site_name":"dbi Blog","article_published_time":"2016-05-12T12:30:36+00:00","og_image":[{"width":878,"height":456,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup00-1.png","type":"image\/png"}],"author":"St\u00e9phane Haby","twitter_card":"summary_large_image","twitter_misc":{"Written by":"St\u00e9phane Haby","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/"},"author":{"name":"St\u00e9phane Haby","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"headline":"SQL Server 2016: Extended Events are more powerful as Trace Flag for Backups and Restores","datePublished":"2016-05-12T12:30:36+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/"},"wordCount":382,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup00-1.png","keywords":["Backup","Microsoft","SQL Server","SQL Server 2016"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/","name":"SQL Server 2016: Extended Events are more powerful as Trace Flag for Backups and Restores - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup00-1.png","datePublished":"2016-05-12T12:30:36+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"description":"Before SQL Server 2016, we use Trace Flag to display information about backup and restore operations like the 3004, 3014, 3213 or 3604. Now, SQL Server 2016 provides 2 new Extended Events: backup_restore_progress_trace & database_backup_restore_throughput","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup00-1.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/SQL2016_backup00-1.png","width":878,"height":456},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-extended-events-are-more-powerful-as-trace-flag-for-backups-and-restores\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2016: Extended Events are more powerful as Trace Flag for Backups and Restores"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b","name":"St\u00e9phane Haby","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","caption":"St\u00e9phane Haby"},"description":"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals &amp; Pharmaceuticals, Banking \/ Financial Services, and many other industries.","url":"https:\/\/www.dbi-services.com\/blog\/author\/stephane-haby\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7902","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=7902"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7902\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/7915"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7902"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7902"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7902"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7902"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}