Earlier this summer I did a workshop on what I consider to be the most important thing that you can do with your database. That is back it up and restores. In my opinion this is job number one as a database administrator. Recently a friend of mine (Tom Roush) ran across a nifty little script that he uses to determine how long it will take for a backup to complete after it has started. Since it is a bit late to include in the workshop I figured that I would post the script here. Just to be clear, I am not the author of the script nor is Tom, but we found it really useful. If you are the author drop me a note so I can pass credit along.
SELECT
percent_complete AS
‘PctComplete’,
start_time AS
‘StartTime’,
command AS
‘Command’,
b.name AS
‘DatabaseName’,
DATEADD(ms,estimated_completion_time,GETDATE())
AS
‘EstimatedEndTime’,
(estimated_completion_time/1000/60) AS
‘EstimatedMinutesToEnd’
FROM
sys.dm_exec_requests a
INNER
JOIN
sys.databases b
ON a.database_id = b.database_id
WHERE command like ‘%backup database%’
AND estimated_completion_time > 0
[...] Backup Times – Script to show how long a backup will take to finish. (Disclaimer – I haven’t tested this.) [...]