Track Robocopy Duration Within SQL Server

Here’s some SQL logic that will track Robocopy Durations. I created this for an ETL process that basically takes a local database backup and copies it to another database server. I then collect the duration in a temp table for reporting of the file copy.

use master;
set nocount on
 
if object_id('tempdb..#robocopy_duration') is not null
    drop table #robocopy_duration
 
create table #robocopy_duration
(
    time_start      datetime
,   time_finish     datetime
,   notes           varchar(max)
)
 
declare @time_start datetime = ( select getdate())
insert into #robocopy_duration ([time_start])
values (@time_start)
 
exec master..xp_cmdshell 'ROBOCOPY "E:\LOAD_ETLS_SOURCE_BACKUPS"  "\\MyDestinationServer\E$\LOAD_ETLS_SOURCE_BACKUPS" LOAD_ETLS_MyDatabase_01.BAK /ETA /Z /XO /R:2 /W:3 /IS /B /COPYALL /NP /LOG:"E:\LOAD_ETLS_SOURCE_BACKUPS\robocopy_log_for_LOAD_ETLS_MyDatabase_01.log"'
exec master..xp_cmdshell 'ROBOCOPY "E:\LOAD_ETLS_SOURCE_BACKUPS"  "\\MyDestinationServer\E$\LOAD_ETLS_SOURCE_BACKUPS" LOAD_ETLS_MyDatabase_02.BAK /ETA /Z /XO /R:2 /W:3 /IS /B /COPYALL /NP /LOG:"E:\LOAD_ETLS_SOURCE_BACKUPS\robocopy_log_for_LOAD_ETLS_MyDatabase_02.log"'
 
declare @begin_time     datetime = ( select max([time_start]) from #robocopy_duration )
declare @time_finish    datetime = ( select getdate() )
update  #robocopy_duration
set     [time_finish]   = @time_finish where [time_start] = @begin_time
 
declare         @get_robocopy_log   table (robocopy_output varchar(max))
insert into     @get_robocopy_log select * from openrowset(bulk N'e:\load_etls_source_backups\robocopy_log_for_load_etls_MyDatabase_01.log', single_blob) as grl
 
update #robocopy_duration
    set notes = ( select robocopy_output from @get_robocopy_log )
    where [time_finish] = @time_finish
 
select
    'time_start'    = left([time_start], 19) + ' ' + datename(dw, [time_start])
,   'time_finish'   = left([time_finish], 19) + ' ' + datename(dw, [time_finish])
,   'duration'      =
         cast(datediff(second, [time_start], [time_finish])/60/60%24 as nvarchar(50)) + ' hr ' +
         cast(datediff(second, [time_start], [time_finish])/60%60 as nvarchar(50)) + ' mn ' + 
         cast(datediff(second, [time_start], [time_finish])%60 as nvarchar(50)) + 's'
,   'notes'         = notes
from
    #robocopy_duration
order by
    [time_start] asc

Of course you could always query the Log output file directly from within SQL Server with this:

declare         @get_robocopy_log   table (robocopy_output varchar(max))
insert into     @get_robocopy_log select * from openrowset(bulk N'e:\load_etls_source_backups\robocopy_log_for_load_etls_MyDatabase_01.log', single_blob) as the_robocopy_log
 
select robocopy_output from @get_robocopy_log

This could be edited further using bulk insert and the appropriate switches to collect only the text you want during the import.