September 22, 2022 at 7:35 pm
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\ ';
September 23, 2022 at 6:40 am
Where is that particular "C:" drive actually located? If it's not on the server, you'll need to us a "share" with a machine name and share name as the first two elements in the path of both the source and the target.
IIRC, you'll need to at least specify a file pattern as a part of the source. "*" for all files, for example.
Of course, if you're just trying to rename a directory, then what you have will do it, provided the "C:" drive is on the server. And, of course, if you ARE loading files onto your server, especially the "C:" drive, then you're committing a very worst practice as best and doing something that could cost you your job if you accidentally rename an OS directory.
Here's the command structure for MOVE, just as a reminder.
Moves files and renames files and directories.
To move one or more files:
MOVE [/Y | /-Y] [drive:][path]filename1[,...] destination
To rename a directory:
MOVE [/Y | /-Y] [drive:][path]dirname1 dirname2
[drive:][path]filename1 Specifies the location and name of the file
or files you want to move.
destination Specifies the new location of the file. Destination
can consist of a drive letter and colon, a
directory name, or a combination. If you are moving
only one file, you can also include a filename if
you want to rename the file when you move it.
[drive:][path]dirname1 Specifies the directory you want to rename.
dirname2 Specifies the new name of the directory.
/Y Suppresses prompting to confirm you want to
overwrite an existing destination file.
/-Y Causes prompting to confirm you want to overwrite
an existing destination file.
The switch /Y may be present in the COPYCMD environment variable.
This may be overridden with /-Y on the command line. Default is
to prompt on overwrites unless MOVE command is being executed from
within a batch script.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 23, 2022 at 7:01 am
I have the accompanying t-sql to move records from 1 registry to another.
I attempted the two variants. work step prevailed without any mistakes, yet records don't move.
any considerations/ideas?
Executive xp_cmdshell ' Move/Y "C:\SomeFiles" "C:\SomeFiles\FileArchive" ';
Executive xp_cmdshell ' Move C:\SomeFiles\ C:\SomeFiles\FileArchive\ ';
September 24, 2022 at 12:00 am
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\ ';
Since you saw fit to post this exact question on another forum instead of doing what I suggested above...
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 "*" 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!!
--Jeff Moden
Change is inevitable... Change for the better is not.
September 24, 2022 at 4:36 pm
As I stated on the other forum - why not use the appropriate subsystem in SQL Server Agent to run the commands directly? Why is it necessary to use a T-SQL step in the agent to run xp_cmdshell to call out to the OS when that is much easier to accomplish using the right subsystem?
There is also a Powershell subsystem that can be used - but I have found that it is a bit problematic.
Here is an example of using the CmdExec subsystem to call out to a Powershell script:
powershell.exe -command "try { & C:\Scripts\MoveFiles.ps1} catch {throw $_}"You can then use a PS script that calls Robocopy to move the files:
$ErrorActionPreference = "Stop";
$fromLocation = "C:\SomeFolder";
$toLocation = "\\someserver\someshare";
$logFile = "C:\SomeLogFolder";
Robocopy.exe $fromLocation "*.*" $toLocation /J /np /ipg:50 /XC /XN /XO | Tee-Object -FilePath $logFile;
$exitCode = $LastExitCode -band 24;
Write-Host "Robocopy Exit Code: $($exitCode)";
exit $exitCode;
You would change the robocopy command line - this command line copies the files, but the idea is the same.
As for your permission issues - you either grant permissions to the agent service account or you create a proxy account with the appropriate permissions. I recommend using a proxy account, that way you can minimize the permissions on that account to only the permissions needed to perform the work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 24, 2022 at 6:56 pm
As I stated on the other forum - why not use the appropriate subsystem in SQL Server Agent to run the commands directly? Why is it necessary to use a T-SQL step in the agent to run xp_cmdshell to call out to the OS when that is much easier to accomplish using the right subsystem?
There is also a Powershell subsystem that can be used - but I have found that it is a bit problematic.
Here is an example of using the CmdExec subsystem to call out to a Powershell script:
powershell.exe -command "try { & C:\Scripts\MoveFiles.ps1} catch {throw $_}"You can then use a PS script that calls Robocopy to move the files:
$ErrorActionPreference = "Stop";
$fromLocation = "C:\SomeFolder";
$toLocation = "\\someserver\someshare";
$logFile = "C:\SomeLogFolder";
Robocopy.exe $fromLocation "*.*" $toLocation /J /np /ipg:50 /XC /XN /XO | Tee-Object -FilePath $logFile;
$exitCode = $LastExitCode -band 24;
Write-Host "Robocopy Exit Code: $($exitCode)";
exit $exitCode;You would change the robocopy command line - this command line copies the files, but the idea is the same.
As for your permission issues - you either grant permissions to the agent service account or you create a proxy account with the appropriate permissions. I recommend using a proxy account, that way you can minimize the permissions on that account to only the permissions needed to perform the work.
My answer would be, what's wrong with an Agent Step that uses xp_CmdShell to do this? It's easily scheduled, etc, etc.
But, shifting gears a bit, doing this from ANYWHERE using ANY METHOD is ABSOLUTELY WROND and no one is bringing up the fact that the OS Drive (C:) of the bloody server is being used as a public spittoon of a file system! These files shouldn't exist anywhere on the server unless the server is specifically be used for Imports and then those files should be on a drive other than the OS drive.
This is also one of the many reasons why, even though xp_CmdShell can be used in a complete safe manner, that xp_CmdShell has been made to suffer the myth of it being a security risk. 🙁
If the OP is the DBA, someone needs to have a serious pork chop dinner with him. If the OP is NOT the DBA, then someone needs to have a serious pork chop dinner the actual DBA. If the DBA is powerless thanks to some really uninformed and foolish Management edicts, the someone needs to have a serious pork chop dinner with the "management" team!
I hope the server is backed up at the file level. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2022 at 3:49 pm
My answer would be, what's wrong with an Agent Step that uses xp_CmdShell to do this? It's easily scheduled, etc, etc.
But, shifting gears a bit, doing this from ANYWHERE using ANY METHOD is ABSOLUTELY WROND and no one is bringing up the fact that the OS Drive (C:) of the bloody server is being used as a public spittoon of a file system! These files shouldn't exist anywhere on the server unless the server is specifically be used for Imports and then those files should be on a drive other than the OS drive.
This is also one of the many reasons why, even though xp_CmdShell can be used in a complete safe manner, that xp_CmdShell has been made to suffer the myth of it being a security risk. 🙁
If the OP is the DBA, someone needs to have a serious pork chop dinner with him. If the OP is NOT the DBA, then someone needs to have a serious pork chop dinner the actual DBA. If the DBA is powerless thanks to some really uninformed and foolish Management edicts, the someone needs to have a serious pork chop dinner with the "management" team!
I hope the server is backed up at the file level. 😉
Nothing wrong with using xp_cmdshell - except security. And no, I don't mean running xp_cmdshell safely - I mean the context of the user running it. Since this is coming from the agent, then the context would be the agent service account - which you then need to grant additional permissions on the server/network/etc. Or - for a non-sysadmin it runs in the context of the owner of the agent job, and now you have different domain accounts owning agent jobs and that leads to a whole lot of other management issues.
Using the CmdExec subsystem allows us to run that step in the context of a defined proxy account with minimal permissions.
As for using the C:\ drive as a file share - agreed. As for using the C:\ to host the scripts - whether they are PS, DOS or some other scripting language? Not really an issue - although I prefer using a drive on the server that is specifically allocated to hosting files which would be only on the SSIS/SSRS type instances.
Powershell/CmdExec are just as easily scheduled and do not require dynamic code to build a string that gets executed. You don't actually need to call out to a script either - you can just embed the code in the job step. Unless you need to query the database for parameters then I don't see any reason to start a session in the database to call out to the OS.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 25, 2022 at 4:25 pm
Nothing wrong with using xp_cmdshell - except security. And no, I don't mean running xp_cmdshell safely - I mean the context of the user running it. Since this is coming from the agent, then the context would be the agent service account - which you then need to grant additional permissions on the server/network/etc. Or - for a non-sysadmin it runs in the context of the owner of the agent job, and now you have different domain accounts owning agent jobs and that leads to a whole lot of other management issues.
You've just outlined the real problem. The problem is NOT with xp_CmdShell. The problem is with the basic security, or rather, the lack of it.
For example, if you have "non-system" admins that are able to affect a job that properly runs xp_CmdShell, you have a serious security issue that needs to be addressed and that would be true even if the job does not currently run xp_CmdShell.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 26, 2022 at 7:49 pm
Jeffrey Williams wrote:Nothing wrong with using xp_cmdshell - except security. And no, I don't mean running xp_cmdshell safely - I mean the context of the user running it. Since this is coming from the agent, then the context would be the agent service account - which you then need to grant additional permissions on the server/network/etc. Or - for a non-sysadmin it runs in the context of the owner of the agent job, and now you have different domain accounts owning agent jobs and that leads to a whole lot of other management issues.
You've just outlined the real problem. The problem is NOT with xp_CmdShell. The problem is with the basic security, or rather, the lack of it.
For example, if you have "non-system" admins that are able to affect a job that properly runs xp_CmdShell, you have a serious security issue that needs to be addressed and that would be true even if the job does not currently run xp_CmdShell.
Maybe I wasn't as clear as I could have been.
To run xp_CmdShell you have to run it in the T-SQL subsystem which does not allow for using a proxy account. Therefore, your only options are the owner of the agent job. If the owner of the agent job is a owned by a sysadmin then xp_cmdshell runs in the context of the SQL Server Agent service account - if it is owned by a non-sysadmin account it will be run in the context of that account.
To get it to work - you then have to add the agent service account as a login to SQL Server and grant permissions, or you need to add a domain account and grant permissions to that domain account and set the owner to that domain account. There are other possibilities - but it involves a lot more setup and configuration to actually make it work.
Using either PS or CmdExec subsystems do allow for proxies though. Setting up the proxy that is granted only the specific necessary access to perform the function is much easier. If running a process to move files - the proxy doesn't need a login to SQL Server. It just needs appropriate file share access.
I am not against using xp_cmdshell - but I also don't see the need to use it in this case.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 26, 2022 at 8:47 pm
and using a sql agent proxy (can be per defined 1 per job) is in a way better than using the xp_cmdshell proxy (system wide).
on a multi-tenant server using the server wide access is not desirable but the SQL agent one is possible as each job (defined by each tenant even if reviewed and deployed by a DBA) will have a proxy account that has access to only the "objects/shares" of the specific tenant.
September 27, 2022 at 12:33 am
and using a sql agent proxy (can be per defined 1 per job) is in a way better than using the xp_cmdshell proxy (system wide).
on a multi-tenant server using the server wide access is not desirable but the SQL agent one is possible as each job (defined by each tenant even if reviewed and deployed by a DBA) will have a proxy account that has access to only the "objects/shares" of the specific tenant.
Now that I agree with and that's the right kind of security thinking I'm talking about.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply