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
sp_configure 'xp_cmdshell', 1
GO
Reconfigure
***************Copy files from one folder to other using xp_cmdshell******************* ***************/
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 @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;
/***************************** **************************/
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('%__/__/____%'
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;
/*****************************
Great Work Dude!!
ReplyDeleteReally good! Thanks for sharing
ReplyDelete