Copy files twice using xp_cmdshell

Question

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).

Answer 1

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;

Answer 2

That is the normal behavior of xp_cmdshell and just command shell. It will not create a new file if a file exists. You have to make them unique. This worked for me.

First create this script and put it in a folder, I use a scripts folder. I named it cptimestamps.ps1.

$src = "C:\cmdshell1\1.txt"; 
$date = Get-Date -Format yyyy-MM-dd-ms; 
$dst = "C:\cmdshell2\1-$($date).txt"; 
Copy-Item $src $dst

Then execute powershell.exe from cmd by using:

powershell.exe -command c:\scripts\cptimestamp.ps1

This worked fine for me. Make sure your powershell execution policy is set. In many environments people just do a set-exeuctionpolicy -executionpolicy Unrestricted