If you are running a DATABASE BACKUP or RESTORE command manually, SQL Server will show you the progress at a specified % completion interval. For the BACKUP, the default is every approximately 10%. You can change that frequency interval by specifying STATS [ = percentage ] option.
BACKUP DATABASE [AdminDBA]
TO DISK = N'O:MSSQL13.SQL2016AG01MSSQLBackupAdminDBA.bak' WITH
NOFORMAT, NOINIT,
NAME = N'AdminDBA-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
But what if the backup/restore was started from a different session that you don't have access (another DBA, scheduled job etc.) or you need more information to troubleshoot issues?
Here I should first mention that there are already couple options to track the progress.
You could review or query the sql server error logs (unless trace flag 3226 is enabled). By default its disabled. You can if that trace flag is enabled using:
DBCC TRACESTATUS(3226);
 |
| DBCC TRACESTATUS(3226) |
If trace flag 3226 is enabled, the successful backup messages are suppressed in the error log.
Or you could use one of the popular DMVs:
SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
Starting with SQL Server 2016, you can turn on the backup_restore_progress_trace extended event to trace both Backup and Restore progress. It comes with rich collection and diagnostic capabilities of extended events that will give you great insight into backup/restore operations and help you troubleshoot any issues better.
Here is how to setup the trace using SSMS:
Connect to the SQL Server then expand all way up to the Extended Events -> Sessions, right click and select New Session Wizard:
Accept the welcome page and on the next page give the session a name. If you choose to you can check box against the Start the event session at Server Startp. I will leave it unchecked though.
??????
There is no built in trace template so leave the Do not use a template selected and click next.
On the next screen, type in "backup" to search in the EventLibrary. select the "backup_restore_progress_trace" then click > to add it to the Selected Events box.
???????
FYI: Here are the data fields that are specific to this event and are automatically captured.
On the next screen it will display list of Global fields if you would like to capture. For this example, I am not selecting any of them.
On the following screen you can add any filters you would like.
Just for the hack of it here, I have added a filter to exclude system databases from the trace.
On the next screen, configure the data storage options. Generally I prefer to store the trace data in file.
Once the trace is started, you can view the Live trace by right clicking the trace and select Watch Live Data.
Or expand the event, select the file, right click and click View Target Data.
Here is sample trace data.
Sample TSQL code to create the extended event trace:
CREATE EVENT SESSION [Monitor Backup Progress] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file(SET filename=N'Monitor Backup Progress',max_file_size=(10))
WITH
(
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=OFF)
GO