Backup Times

 

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