Post reply

SQL Server Copy not working

  • July 10, 2006 at 9:35 am

    #173312

    I am trying to copy files using SQL Server jobs and using following command

    Type: Transact SQL

    Database: Master

    EXEC xp_cmdshell 'copy S:\Program Files\Microsoft SQL Server\DBA\tlogbackup\bes4\*.*  \\006-pm2-fs01\Groups\CIS\CIS DBA\TransactionLogs\'

    Job succeeds but it doesnt copy any files over. I have the read/ write privilege to the remote location.

    Any idea what could be wrong here?

    thanks

  • July 10, 2006 at 10:28 am

    #648289

    The server service account needs read write privleges.

  • July 10, 2006 at 1:47 pm

    #648319

    You need to surround your paths with double quotes.  

  • July 10, 2006 at 1:50 pm

    #648321

    Service account (SQL Server Startup Account)  needs Read /Write as Steve correctly said, but it is Read /Write for BOTH NTFS and Share Permissions. Share permissions would be called "Change" for that. Did you create a share at all?

    Regards,Yelena Varsha

  • July 11, 2006 at 1:07 am

    #648369

    just my 2ct

    - I use this step in a job:

      execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID , @step_id = 9, @cmdexec_success_code = 0, @on_success_action = 1, @on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 1, @os_run_priority = 0, @flags = 0, @step_name = N'CopyToBackupSrv', @subsystem = N'CmdExec', @command = N'XCOPY C:\MSSQL\BACKUP\*.* \\myuncname\myhiddenshare$\myservername\Database\ /c /k /h /v /y'

    - delete the files at your destination before you copy or add the /Y parameter to your dos-command to be able to overwrite. (the delete path should be performing better  )

    - As I'm told xcopy would perform better than copy

    - like already mentioned if you have blancs in your path you should embed the full path with doubble quotes.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • July 12, 2006 at 12:02 pm

    #648779

    Or:

    begin

    declare @sql nvarchar(1024)

    set @sql='xcopy "d:\mssql\BACKUP\*.bak" \\MyServer\backup  /V /R /K /Y /Z /I'

    EXECUTE xp_cmdshell @sql

    end

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply