SQL Server – Copy Files Twice Using xp_cmdshell

sql serverxp-cmdshell

When I run this command

exec xp_cmdshell 'copy C:\---\cmdshell1\a23.txt C:\---\cmdshell2';

It copies a23.txt file from cmdshell1 to cmdshell2 folder.

When I run the same command second time it does not copy the same a23.txt file to folder cmdshell2 for second time.
So after running xp_cmdshell command multiple times I only get one file in cmdshell2 which was generated the first time.
My question is, how to write this command in order to copy the a23.txt file from cmdshell1 folder into cmdshell2 the amount of times I ran the query(if I have run the above query 3 times, I want to have 3 copies of a23.txt file in cmdshell2).

Best Answer

Here is an example of building a dynamic xp_cmdshell command that includes a timestamp on the file

declare @Cmd varchar(100)
set @Cmd = 'copy C:\test\a23.txt C:\test\a23-' + replace(replace(convert(varchar(19),sysdatetime()),' ','-'),':','-') + '.txt'
print @cmd
exec xp_cmdshell @cmd;
Related Question