Displaying Backup or Restore Progress

Have you ever started a database backup or a restore process that runs a long time then wanted to know when it will complete?   If you have then there is an easily way to do this.  Knowing when a database backup or restore operation will completes provides you valuable information, especially when you need to perform follow-on tasks that are waiting for the backup or restore process to complete.

In order to show the backup or restore status you can run the following TSQL statement:

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'); 

By reviewing this query you can see it is using sys.dm_exec_requests and sys.dm_exec_sql_text to retrieve information for “BACKUP DATABASE” or “RESTORE DATABASE” commands.

Below is a sample of the output from this command that I have reformatted for readability:

SPID   command                          Query                                                                                                                                                                                                                                                            
------ -------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------- 
60     BACKUP DATABASE                  BACKUP DATABASE [AdventureWorks2016CTP3] TO  DISK = N'C:AdventureWorks2016CTP3.bak' WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2016CTP3-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10                                       
 
 
start_time                         percent_complete     estimated_completion_time
-----------------------            ----------------     -------------------------
2017-03-19 10:11:51.630            43.9362              2017-03-19 10:12:13.460

By reviewing the “percent_complete” column above you can see my backup is 43.9362 percent complete, and then by using the “estimated_completion_time” column you can tell my backup will complete around 10:12:13.460.  You can use the “Query” column  in the output to see the actual BACKUP command that was used to back up my Adventure Works2016CTP3 database.

See all articles by Greg Larsen