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

It should spit out something like this — a row per running backup, with the running time, the time left to finish, the wall-clock time it should be done, the percent complete, and the command itself (e.g. BACKUP LOG ...).

Isn’t that just handy dandy.