Copy all files from one drive to other via SQL Server Script using Robocopy

Today over web user was looking some option to automate copying of files from one location to another. Using robocopy is easy but if every time file name changes you need some script to do this for you.

Below script uses robocopy to copy all files between servers or drives.
If you want to use xp_cmdshel, refer : Copy all files via SQL Server Script xp_cmdshell

Don't forget to change

• Source location
• Destination location
• Log location (logs for robocoy status)

/*********Copy all files from one folder to other using  Robocopy *****************************/

DECLARE @path varchar(100)
DECLARE @SourceFile AS VARCHAR(500);  
DECLARE @DestinationFile AS VARCHAR(500)
Declare  @name as varchar (500);  
DECLARE @Cmd AS VARCHAR(1000); 
DECLARE @log AS VARCHAR(500);  
DECLARE @i as  int
Declare @a as int

SET @SourceFile = 'U:\Adhoc_Backups\';
SET @Path = @SourceFile --> Pass the path in which you want to search
SET @cmd = 'dir ' + @path + ' /A /OS /-C'
SET @DestinationFile = 'U:\Saurabh\'

Set @log = 'U:\Saurabh\log'

CREATE Table #MyTable (Results varchar(500))
CREATE Table #MYFILES (create_date datetime ,is_directory int ,[Name] varchar 
500))
CREATE Table #MYFILES_main (id int identity , name  varchar (500))

INSERT INTO #MyTAble
EXEC XP_CMDSHELL @cmd

INSERT INTO #MYFILES --(create_date datetime ,is_directory int ,[Name] varchar 
500))
SELECT LEFT(Results, 20) [create_date],
  CASE LTRIM(RTRIM(SUBSTRING(Results, 21, 18))) WHEN ''
  THEN 1 ELSE 0 END as is_directory,
  SUBSTRING(Results, 40, Len(Results)) AS [name]
FROM #MyTable
WHERE ISNULL(PATINDEX('%__/__/____%', Results), 0) != 0

DELETE FROM #MYFILES where name in ('..','.') or is_directory =1

insert into #MYFILES_main
select name from  #MYFILES

Select @a = count (*)  from #MYFILES_main
Set @i=1

While @a >= @i               
  Begin
    select @name=name from #MYFILES_main where id=@i               
  
    /* build copy command */  
    SET @Cmd = 'ROBOCOPY ' + @SourceFile + ' ' + @DestinationFile + '  ' + @name + 'r:2 /log:' + @log
    Set @cmd = @cmd + cast(@i as varchar(5))+'.txt' ;
    select  @cmd
    SET @i= @i+1
    EXEC master.dbo.xp_cmdshell @Cmd;

/*EXEC master.dbo.xp_cmdshell 'COPY U:\Adhoc_Backups\Saurabh1.txt U:\Saurabh\'*/

  End 

DROP TABLE #MyTable
Drop Table #MYFILES
Drop table #MYFILES_main

/*******************************************************/