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

(Note: If you are downloading this file, watch copy/paste line wrapping..)