Unanswered: Copy the latest diff backup from one server to another.
SQL Server 2012-SP2 and Windows 2008R2
I need to copy the last differential backup file from the production server directory to a data warehouse server directory. There are 3 differential backup files on the directory and I need to grab the latest one. I have the following syntax which work from a batch file but it does not work when I put it into a job step as a cmd even though I remove the double %% to only one %. Can some one please help me resolve this ?
set path1="\\192.29.305.213\Z$\Sqlserver\Production\Ba ckups\KBR_PROD"
for /f "tokens=*" %%a in ('dir /b /a-d /o-d "%path1%\DIFF*.bak"') do copy "%path1%\%%~a" "%path2%" & goto nextstep
Echo File Copied.
Even when I change it to (only one % for the variable),
for /f "tokens=*" %a in ('dir /b /a-d /o-d "%path1%\DIFF*.bak"') do copy "%path1%\%~a" "%path2%"
It does not work..........It runs successfully but no file is copied or I get
Executed as user: PROD23\Sqladm024. The process could not be created for step 1 of job 0xEAAF943771FF304A9E7AD8ADAC24F96C (reason: The system cannot find the file specified). The step failed.
I know that the file is there. It works with batch file. Poweshell script can be OK. No SSIS - Not installed
I usually end up throwing all of the commands into a batch file, and having SQL Agent run the batch file.
You could be running into User Access Control. Does the SQL Agent account have rights on both directories? Toss a cd and a echo %username% command near the top just to see who you are running as, and where.
Thanks. I tried that but it did not work either. It runs successfully but copies no file. The job runs under Agent account but uses a proxy account where proxy account is a local Admin on both servers............Thanks........
Then you may want to throw in an echo command to see how the copy command is coming out. I am betting that is getting messed up in some way. The extra space in the path Backups above is just a typo, right?