SQLTeam.com | Weblogs | Forums

Agent job with xp_cmdshell to move files

i have the following t-sql to move files from 1 directory to another.

I tried both versions. job step succeeded with no errors, but files do not move.

any thoughts/suggestions?

EXEC xp_cmdshell ' Move /Y "C:\SomeFiles" "C:\SomeFiles\FileArchive" ';

EXEC xp_cmdshell ' Move C:\SomeFiles\ C:\SomeFiles\FileArchive\ ';

xcopy (faster; from the box containing the file, not a client box), verify file is good, then del

You can tell if the files are good by comparing the byte totals, or, if you're the nervous type, by doing actual file comparison(s). I think Windows has a program that compares files.

Robocopy is another option and has more features than xcopy. It has good logging and progress features, however those aren't conducive to being called from xp_cmdshell. It has a /MOV option to move files, and I think uses a faster protocol than xcopy does (or did in the past). It also has a restartable option, in case you have large files and spotty connectivity. Be advised that this option can be significantly slower to use.

Generally, if a file isn't moved it means the account/user running the command doesn't have file delete permissions on the source disk/folder.

Since the OP mentioned running from Agent - I would not use xp_cmdshell. I would recommend using the command subsystem and execute the copy command directly. You can even use powershell to execute robocopy or xcopy and redirect output as needed.

1 Like

Have you actually tried executing those commands in a command prompt window to see if the MOVE works? The reason I ask is because I posted the syntax for the MOVE command over on SQLServerCentral that stipulates that your first path MUST stipulate either a single file name or a "*" for all the files. When you run your code above in the SSMS code window, you WILL get a "The system cannot find the file specified." error because you have specified neither a single file name nor a file pattern in sour "source" part of either of your commands.

If your intention is to move all the files, add a bloody "*" (like I said before and again on the other forum and like I've said again above) to indicate that as a part of your source directory specification!!!

The reason why SQL Agent never produced an error is because it successfully executed xp_CmdShell and successfully came back from that. You did nothing to detect an error made by the MOVE command itself!!

Ok, i'm now trying running the xp_cmdshell in the query window instead of the agent with the following command:

EXEC xp_cmdshell 'Move /Y C:\SomeFiles*.* C:\SomeFiles\FileArchive'

Access is denied.

And then trying to break up the 'move' into 2 steps: xcopy and delete

'Xcopy C:\SomeFiles*.* C:\SomeFiles\FileArchive' - copy fine no problem.

'del /Q /S C:\SomeFiles*.**' - access denied. files in \FileArchive were deleted.

So it appears i have no move/deletion permission in C:\SomeFiles.

The MOVE command is still incorrect. This is why I posted the actual full command syntax on the other forum.

The correct form of the command, for what you're trying to do, can be either of the following...

EXEC xp_cmdshell 'Move /Y C:\SomeFiles\*.* C:\SomeFiles\FileArchive';
EXEC xp_cmdshell 'Move /Y C:\SomeFiles\* C:\SomeFiles\FileArchive'

As for your privs problem, this is why I asked where the "C:" drive you're using actually is. You should answer the questions of people that are trying to help you. :wink:

The fact is, the command above, no matter which machine you run it from, is going to look at the "C:" drive of the SQL Server itself. Do you have privs there? I would think not. Such files should EVER exist on the "C:" drive of your server.

Use robocopy within powershell in a sql job forget these xp_cmdshell commands

Forget PowerShell and you don't need RoboCopy for something so simple. :yum: These "xp_CmdShell commands" are actually known as DOS commands, work at the command prompt without xp_CmdShell (just like PowerShell except you don't need to start PowerShell to use them) and they're tried, true, and tested by time. :wink: If someone can't even get the MOVE command to work, how do you think they're going to do with PowerShell and RoboCopy?

The real problem here, though, is not the choice of any tool because they'll all get the job done. The real issue that everyone keeps avoiding is that the whole concept here is wrong. You shouldn't be using the "C:" drive/OS drive of your server as a bloody file server.

1 Like