Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2013
    Posts
    23

    Unanswered: Copy the latest diff backup from one server to another.

    Hi All,

    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 ?

    echo @off
    set path1="\\192.29.305.213\Z$\Sqlserver\Production\Ba ckups\KBR_PROD"
    set path2=K:\Sqlserver\Datawarehouse\Backup\PROD
    for /f "tokens=*" %%a in ('dir /b /a-d /o-d "%path1%\DIFF*.bak"') do copy "%path1%\%%~a" "%path2%" & goto nextstep
    :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

    Message
    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
    Last edited by cdx28; 10-09-14 at 13:13.

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Have you mapped the destination drive?

  3. #3
    Join Date
    Jul 2013
    Posts
    23
    Thanks for the response. I am trying to make this a automated process and not a manual process. It does not work with the mapped drive either anyways.
    Last edited by cdx28; 10-09-14 at 15:56.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  5. #5
    Join Date
    Jul 2013
    Posts
    23
    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........

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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?

  7. #7
    Join Date
    Jul 2013
    Posts
    23
    Yes. It is a typo. Thanks. If I run the execute batch file in Query window, I am getting "Access denied" it may have something to do with that. Cmd_exec is enabled.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •