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

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

Below script uses xp_cmdshell to copy all files between servers or drives.

If you want to use Robocopy please refer : Copy all files via SQL Server Script using Robocopy

 Don't forget to change

• Source location
• Destination location
• Enabling xp_cmdshell from sp_configure

sp_configure 'xp_cmdshell', 1
GO
Reconfigure
 
***************Copy files from one folder to other using xp_cmdshell**********************************/

DECLARE @path varchar(100); 
DECLARE @SourceFile AS VARCHAR(500);  

DECLARE @DestinationFile AS VARCHAR(500); 
Declare  @name as varchar (500);  
DECLARE @Cmd 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\' ; 
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 
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 ;
Set @i=1 ;
Set @a =@a+1 ;

While @a >= @i                  
  Begin
    select @name=name from #MYFILES_main where id=@i  ;               
    /* set destination file's fullpath */
    SET @SourceFile = @path;  
    SET @SourceFile = @SourceFile +  @name 

    /* build copy command */  
    SET @Cmd = 'COPY ' + @SourceFile + ' ' + @DestinationFile; 
    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; 

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