When executing a backup from a t-sql command, it is difficult to know what percentage has completed and when the backup will be complete.
When you are taking a backup, you can specified STATS = 10 like following.
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\MYBackup\AdventureWorks2012.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
After each 10% increment of completion it give an output as follows.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 24408 pages for database 'AdventureWorks2012', file 'AdventureWorks2012_Data' on file 14.
100 percent processed.
Processed 2 pages for database 'AdventureWorks2012', file 'AdventureWorks2012_Log' on file 14.
BACKUP DATABASE successfully processed 24410 pages in 5.320 seconds (35.846 MB/sec).
However, if you are running backups for large databases to complete 10% will take long time you don’t have any idea when it will finish.
However, by using the database system view and the dm_exec_requests DMV you can get exact values.
SELECT d.name databasename,
r.command,
r.percent_complete,
r.session_id,
r.start_time,
estimated_finish_time = DATEADD(MILLISECOND, estimated_completion_time, CURRENT_TIMESTAMP)
FROM sys.dm_exec_requests r
INNER JOIN sys.databases d
ON r.database_id = d.database_id
WHERE r.command like
'%Backup%'
In the above query most of the columns are self-explanatory except the estimated_finish_time column. This is derived using estimated_completion_time of the sys.dm_exec_requests DMV. This value is given in milliseconds and by using DATEADD functions, the finish time was estimated.
The output of this query is following.
]]>