Agent Jobs

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.

Advertisements

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