November 17, 2011 at 9:57 am
I have a Maintenance Plan which runs fine and is scheduled under a SQL Agent job. In the properties of that SQL Agent job, I've added a second step which calls a CMD script (Operating System (CMDExec)). That CMD script uses Robocopy to MOVE the backup files to a repository.
All good so far, the SQL Agent jobs runs both steps (MP and CMD script) as per the schedule and completes successfully. However, the script does not actually execute, I know that because I've built in logging to the CMD script, no logfile is generated and also the Backup files do not move across to the Repository. When I watch the job occur, a command line window opens but then immediately closes with no logging in Event Viewer or SQL Agent history (as I say the job completes successfully). Also, nothing is written to the SQL Agent job Output File for that step.
If I log-in to the server using the SQL Agent account and manually execute the script by double-clicking on it it runs fine. It also runs fine if use Task Scheduler to execute the job.
I am at a loss to explain this can find nothing relavent using Google Search or even this website. Please can anybody suggest anything I might be missing as I really need this Job Step to run immediately after a successful Maintenance Plan and not as an independant Task.
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Many thanks
November 17, 2011 at 1:46 pm
Hi,
What is the syntax of your second step?
November 17, 2011 at 2:14 pm
Yes please post the code...
Also, do you have your proxy account set up for your SQL Server Agent Service Account? I'm pretty sure that it acts the same was a running an xp_cmdshell. Typically when you can run it as yourself but not as the Service account that runs SQL Agent job there's a security issue lurking. Also, the account being used for the proxy/service account generally needs to be a local admin on the server it runs on (as most accounts cannot move/delete files without being an admin on the box)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 18, 2011 at 7:29 am
CLS
REM - SCRIPT TO MOVE BACKUPS FROM C:\Backup\ directory to \\<server>\SUK01SQL01_Backups\ path.
REM
REM ---blurb---
REM
REM
SET LOGFILE=.\ROBCOPY_LOG.TXT
SET SOURCE=c:\BackupSET DESTINATION=\\<source_server>\S<dest_server>_BACKUPS
ECHO %DATE%-%TIME% Robocopy Began > %LOGFILE%
ECHO >> %LOGFILE%
C:\Windows\System32\Robocopy.exe %SOURCE% %DESTINATION% /E /z /MOV /NP >> %LOGFILE% 2>&1
ECHO >> %LOGFILE%
ECHO %DATE%-%TIME% Robocopy ended >> %LOGFILE%
November 18, 2011 at 7:30 am
In Addition, I created a proxy which uses my own personal local admin credentials and selected that proxy for this job step. Unfortunately the same thing happens.
November 18, 2011 at 8:46 am
When you first ran the robocopy, were you prompted with anything like "this is freeware, click OK..." or "please register", or something else (anything that resulted in a pop-up box?
If so, I've run into situations were this happened and in order to get it working properly I've had to run it via the command line as the user who would be executing it before it would actually work via an Agent job.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 18, 2011 at 8:50 am
Robocopy isn't freeware, it now comes packed with Windows (it was a separate download previously). Also, when I log in as any user (SQL Agent or myself) and I double-click on the script it works fine. It also works fine as part of a Windows Scheduled Task.
November 18, 2011 at 8:56 am
It doesn't matter. I use a program from sysinternals and in order to get that to run I had to first manually run it from the command line and in one case, set a parameter to /nolicprompt (don't prompt about the license).
Just throwing it out there.
On a side note, why don't you take your CmdExec code and throw it into a batch file and have it run via the Task Scheduler?
Sorry about this suggestion, I realized after I posted that it wasn't what you were asking for in the first place...apologies!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 18, 2011 at 9:44 am
I'll try running the CMD line as a one-liner in command prompt and see if that makes any difference.
The script is a batch script which as I say runs fine if you double-click on it. It also runs fine when executed by the Task Scheduler. I created the second step in the SQL Agent Job, select CMDExec then clicked the 'Open' button and selected the batch file. It then fed in the CMD line to the SQL Agent job step.
November 18, 2011 at 9:52 am
Try something like this from the command line:
C:\Program Files\robocopy>Runas /user:YOURDOMAIN\YourUser "YourCommand"
Example:
C:\Program Files\7-Zip>Runas /user:mycompany\mydoggiejessie "7z.exe a c:\test71.zip c:\ziptest\* -mx9 -r"
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 18, 2011 at 9:54 am
Try adding an output file on the advanced form of your job. Maybe it will reveal a clue. Or try running the script via xp_cmdshell while in query analyzer. Again, maybe you'll get an error message in the results pane.
November 21, 2011 at 9:39 am
Thanks but I've already advised the output file returns nothing: "nothing is written to the SQL Agent job Output File for that step." (because the job completes successfully).
With regards to the RUNAS, I'm not happy with specifying username/password in clear text anywhere. The SQL Agent proxy I set up should take care of that anyway.
I'm at a loss, this is either a bug in SQL Agent or else a security-flaw in Windows 7. Thanks everyone! :0)
November 21, 2011 at 9:46 am
With regards to the RUNAS, I'm not happy with specifying username/password in clear text anywhere. The SQL Agent proxy I set up should take care of that anyway.
No need to worry about the clear text user/pwd. That's a one time deal that you do yourself...using the credentials from of the proxy account. I know it sounds weird...but try it.
In reference to what I was talking about earlier with a pop-up....take when I run a 7zip application for the first time...it prompts me to click OK the first time running...or in Windows 7's case, you get the irritiating security pop-up stating something along the lines of "are you sure?"
When the Proxy account executes something, it has no control over the pop-up and doesn't know how to handle it. You need to use the command line to run it exactly as the proxy account is running it so you can see if there's a pop-up being generated. This only needs to happen once (that I am aware of). Once you've done it, the SQL Agent job should run just fine (assuming a pop-up is actually causing the issue in the first place)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 21, 2011 at 9:59 am
Just tried that now. Opened a command-prompt; CD <scriptdir>
c:\Scripts>C:\Windows\System32\Robocopy.exe c:\Backup\ \\<servername>\<repositorypath>\ /E /z /MOV /NP >> .\ROBCOPY_LOG.TXT
and nada.
The log file (.\ROBCOPY_LOG.txt) did not populate BUT the Output file DID update with a funny symbol (which resembles a little match-stick man.) Weird!
November 21, 2011 at 10:14 am
I didn't see the runas command in there... did you omit that on purpose?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply