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


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

2 comments:

  1. the only problem with xp_cmdshell is that it does not return an error if it encounters a file system error (e.g. file does not exist).

    ReplyDelete
    Replies
    1. True , but this script first find the file list save it and then copy. The only chance is if some one delete file just after my script make list of files.

      Delete