Backup Times

Posted: August 24, 2009 in Uncategorized

 

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

 

Comments
  1. […] Backup Times – Script to show how long a backup will take to finish. (Disclaimer – I haven’t tested this.) […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s