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.
