This script will copy all files to a folder from a remote folder.
It is useful for copying backup files from a remote system for disaster recovery, test restores and log shipping.
It should be used in conjunction with the backup procedures on this site as it expects file names in that format (xxxxxxyyyymmdd_hhmmss.xxx).
It expects can take local folders and will copy files that are not present on the local system.
The two local folders is so that the files can be processed in the first folder then moved to the second.
There is a retainperiod parameter - no files created befor that period will be transferred - this is so that the local files can be archived.

When run it will copy any applicable files into @LocalDir1.
It should be scheduled to run depending on the files to be transferred.
If daily backups are to be transferred then schedule it after the backup - maybe 6 hourly in case of problems.
If tr log backups are to be transferred then schedule with the same frequency for log shipping.




drop proc s_nrSyncDir
go
create proc s_nrSyncDir
@LocalDir1	varchar(128) ,
@LocalDir2	varchar(128) ,		-- assume files are processed in @LocalDir1 then moved to @LocalDir2 by some other process
@RemoteDir	varchar(128) ,
@FileMask	varchar(128) ,
@RetainPeriod	datetime
as
/*
exec s_nrSyncDir
	@LocalDir1 = 'c:\BackupCopy\MyServer\Log\' ,
	@LocalDir2 = 'c:\BackupCopy\MyServer\Log\Archive\' ,
	@RemoteDir = '\\MyServer\c$\Backup\Log\' ,
	@FileMask = '*.bak' ,
	@RetainPeriod = '19000105'	-- Only transfer backups created in the last 5 days
*/
declare @cmd varchar(1000) ,
	@filename varchar(128) ,
	@EarliestRetain varchar(15)
	
	select	@EarliestRetain = convert(varchar(8),getdate() - @RetainPeriod, 112) + '_' + replace(convert(varchar(8), getdate() - @RetainPeriod, 108), ':', '') 
	
	create table #locdir (s varchar(2000))
	create table #remdir (s varchar(2000))
	
	select @cmd = 'dir /B ' + @LocalDir1 + @FileMask
	insert #locdir exec master..xp_cmdshell @cmd
	
	if @LocalDir2 is not null
	begin
		select @cmd = 'dir /B ' + @LocalDir2 + @FileMask
		insert #locdir exec master..xp_cmdshell @cmd
		
		delete #locdir
		where s is null
		or s like '%not found%'
		
		-- delete any out of date files in @LocalDir2
		select @filename = ''
		while @filename < (select max(s) from #locdir)
		begin
			select @filename = min(s) from #locdir where s > @filename
			
			if left(right(@filename,19),15) < @EarliestRetain
			begin
				-- delete file
				select @cmd = 'del ' + @LocalDir2 + @filename
				select @cmd
				exec master..xp_cmdshell @cmd
			end
		end
	end
	
	delete #locdir
	where s is null
	or s like '%not found%'
	
	-- delete any out of date files @LocalDir1
	if @LocalDir2 is null	-- If there is a @LocalDir2 then need to leave files until they are processed
	begin
		select @filename = ''
		while @filename < (select max(s) from #locdir)
		begin
			select @filename = min(s) from #locdir where s > @filename
			
			if left(right(@filename,19),15) < @EarliestRetain
			begin
				-- delete file
				select @cmd = 'del ' + @LocalDir1 + @filename
				select @cmd
				exec master..xp_cmdshell @cmd
			end
		end
	end
	
	select @cmd = 'dir /B ' + @RemoteDir + @FileMask
	insert #remdir exec master..xp_cmdshell @cmd
	
	delete #remdir
	where s is null
	or s like '%not found%'
	
	delete	#remdir
	where	left(right(s,19),15) < @EarliestRetain
	
	delete #remdir
	from #locdir
	where #remdir.s = #locdir.s
	
	drop table #locdir
	
	select @filename = ''
	while @filename < (select max(s) from #remdir)
	begin
		select @filename = min(s) from #remdir where s > @filename
		
		select @cmd = 'xcopy "' + @RemoteDir + @filename + '" "' + @LocalDir1 + '"'
		select @cmd
		exec master..xp_cmdshell @cmd
	end
go