New extended event to track backup and restore progress

First published on MSDN on Sep 22, 2015

Backup and restore are long running tasks in SQL Server with limited insights into progress. Often a question asked is "How much longer will it take for this operation to complete?". Though there are trace flags and DMVs that provide some information, they are either unstructured (ex: trace flags send output to error logs and can be hard to parse) or hard to interpret.

In SQL Server 2016 (starting with CTP 2), we have introduced a new extended event that can be used to easily gain insight into progress of any of these long running activities. In addition, you can leverage the rich collection and diagnostic capabilities of extended events for advanced analysis. We hope that this extended event will simply your troubleshooting experience with backup and restore activities.

Single Extended Event

You can turn on the backup_restore_progress_trace extended event to trace both Backup and Restore progress.

CREATE EVENT SESSION [Backup trace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file(SET filename=N'Backup trace')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

The event has the following data that is part of the payload.

name type_name                           description
operation_type    database_operation_type     Type of operation - Indicates whether the database is being backed up or restored
trace_level           backup_restore_trace_level   Backup/Restore trace level
database_name   unicode_string                      Logical name of the database
trace_message     unicode_string                      Progress trace messages for key steps in backup or restore

name                                   map_key    map_value
database_operation_type     0                 Backup
database_operation_type     1                 Restore
backup_restore_trace_level   0                 Information of major steps in the operation
backup_restore_trace_level   1                Verbose I/O related information

Sample Output from a backup process with key information and phases highlighted:

Sample Output from a restore process with key information and phases highlighted:

You can get the latest SQL Server 2016 CTP bits here to play with this feature.

Ajay Jagannathan ( @ajaymsft )
Principal Program Manager