How far is that SQL backup?

Good question, and normally it gets accompanied with some shouting.

We normally tend to stagger our workloads into something that resembles a lot of backups in a row and some smart index rebuilds followed by some more backups. But that is not really the point of this little post.

I had a situation where I needed to do a “quick” backup on 500GB Database. Needless to say I wanted to tell everyone to bugger off my server for some time and I wanted to know exactly how long…

..‘Cause that’s how I roll.

Well here is a little script to do exactly that for you:

SELECT
T1.NAME
, CONVERT(varchar,DATEADD(ms,T2.TOTAL_ELAPSED_TIME,0),108) [Running Time]
, CONVERT(varchar,DATEADD(ms,T2.ESTIMATED_COMPLETION_TIME,0),108) [Time To Finish]
, CONVERT(varchar,DATEADD(ms,T2.ESTIMATED_COMPLETION_TIME,GETDATE()),108) [Should Be Done@]
,T2.PERCENT_COMPLETE as [% Complete]
,(SELECT TEXT FROM sys.dm_exec_sql_text(T2.SQL_HANDLE))AS COMMAND FROM
MASTER..SYSDATABASES T1, sys.dm_exec_requests T2
WHERE T1.DBID = T2.DATABASE_ID AND T2.COMMAND LIKE '%BACKUP%'
ORDER BY percent_complete DESC,[Running Time] DESC

I should spit out something like this..

Running Time   Time To Finish   Should Be Done@% Complete   COMMAND00:05:20   00:00:3116:19:5391.04348   BACKUP LOG Blah Blah..NAMEmyDB

Isn’t that just handy dandy.

Adrian