SQL Server Performance

xcopy not working

Discussion in 'General DBA Questions' started by Raulie, Mar 9, 2004.

  1. Raulie New Member

    EXEC master..xp_cmdshell 'xcopy "D:program FilesMicrosoft SQL ServerMSSQLBACKUPDB_backup_device.bak" \SecondServerShare /C'

    When this step executes from the Agent it runs with Success, however when I check the share there is nothing there. Has anyone had this problem?
  2. satya Moderator

    Have you tried it manually from command prompt?
    Ensure the SQLAgent service account has required privileges on the specified share.
    Also try with normal COPY command.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. Twan New Member

    also make sure that the SQLAgent service account has the privilege to execute xcopy.exe. This sort of thing has tripped me up in Windows Server 2003

    perhaps you can change the job step slightly so that any output is put into a file for you to look at? on the advanced tab from memory

  4. Raulie New Member

    Thanks Satya and Twan,
    I tried it from the command line and it executes but there is still nothing in the backup folder on the secondary server. Both accounts have correct prevliges. When I configure the job to run as a OS command like xcopy "D:program FilesMicrosoft SQL ServerMSSQLBACKUPDB_backup_device.bak" "\SecondServerShare" /C /Y or xcopy "D:program FilesMicrosoft SQL ServerMSSQLBACKUPDB_backup_device.bak" "\ServerNameD$Program FilesMicrosoft SQL ServerMSSQLackup" /c /y I get invalid drive specification I'll double check with our NT Admin to see permissions again.
  5. Raulie New Member

    Correction from Command line when I execute xcopy "D:program FilesMicrosoft SQL ServerMSSQLBACKUPDB_backup_device.txt" "\SecondServerShare" /C /Y the file gets transfered, but when I use this same command from SQL Agent on Status it reads job executing but nothing happens after a few minutes or so I cancel out. I created a text file called DB_backup_device.txt and use this to test the xcopy so I dont think it should take that long to transfer a blank text file. By the way is the syntax correct it is the same one I am using in command line.
  6. satya Moderator

    Post the code used in the job, for the syntax information run XCOPY /? on command line (ages ago I've used xcopy).

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. Raulie New Member

    I have tried different way in sql agent here are some examples

    These two give me invalid drive specification.
    xcopy "D:program FilesMicrosoft SQL ServerMSSQLBACKUPDB_backup_device.txt" \IPAddressShare/C /Y

    xcopy "D:program FilesMicrosoft SQL ServerMSSQLBACKUPDB_backup_device.txt" "\ServerNameD$Program FilesMicrosoft SQL ServerMSSQLackup" /c /y

    This one executes successful but the output file also reads invalid drive specification
    EXEC master..xp_cmdshell 'xcopy "D:program FilesMicrosoft SQL ServerMSSQLBACKUPDB_backup_device.txt" \IPAddressBACKUP /C /Y'

    I think the sytanx is correct the drive letter is correct, I cant seem to figure it out I am using the same account when I execute from command line which works.

  8. x002548 New Member

    What your security model?


  9. Raulie New Member

    The only thing I can think of is some type of permission issue. SQL Server Service and Agent are running on same account with administrative rights to the share. I am having our Network Admins look into this. I know the syntax is correct because I've tested the xcopy from other machines and they work fine.
  10. satya Moderator

    Check any firewall is involved or monitor from network side.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. Raulie New Member

    Satya I think your right. I was wondering when transfering a file from server to server is it using NetBios to make the transfer with xcopy?
  12. tdong New Member

  13. tdong New Member

    here is a little script run every night I created which copy directly from the server for back up instead of a .bak I hate dot bak that is all

    by the way robocopy usage is the same as xcopy dos command promt.
    ROBOCOPY source destination /MIR ---copy as mirror which I find best
    definition source,destination : (drive:path or \serversharepath)

    net stop MSSQLServer
    robocopy -----all the database----
    net start MSSQLServer

    May the best cheaters win
  14. Raulie New Member

    Thanks As a work around I ended up creating a .bat file which i scheduled using the Windows Schedule Task Manager. I would have have liked to use the Agent to run all my steps so that I can have a history and have more control of the work flow of my job, but I'll need to use my work around until I find out whats happening with xcopy.
    By the way do you include the net stop commands??
  15. Twan New Member

    check the permission of xcopy.exe itself. I'm pretty sure that by default it has execute rights only for system, administrators and interactive. xcopy itself won't inherently use netbios, but the UNC may try to use netbios (especially if run from a non Windows 2003 server)

    net start/stop will need an administrative account to succeed

  16. tdong New Member

    net start/stopn was intend for me to use in my case (beside nobody using my mssql server from 5pm to 8am) since I don't want a .bak I copy a back up directly from the source MSSQL folder.

    May the best cheaters win
  17. tdong New Member

    By the way why don't you use the back up schedule in the SQL Server Enterprise Manager you can start the server agent in there and set a schedule to write a back up directly to your share drive no need to use xcopy. if the back up already exists and you want to make another copy to your drive then use robocopy or xcopy to copy it over. you can test your command with DOS first before make a .bat file

    May the best cheaters win
  18. tdong New Member

    try to use ROBOCOPY in place of your XCOPY I am pretty sure it work

    May the best cheaters win
  19. Raulie New Member

  20. tdong New Member

    /MIR make the exact image directories etc and override the destination with source

    May the best cheaters win
  21. tdong New Member

    The list in under C:program FilesWindows Resource KitsTools
    obocopy.doc when you install the kits<br /><br />SwitchFunction Performed<br /><br />/XLExcludes Lonely files and directories.<br />/ITIncludes Tweaked files.<br />/ISIncludes Same files.<br />/XCExcludes Changed files.<br />/XNExcludes Newer files.<br />/XOExcludes Older files.<br />/XX Suppresses reporting and processing of Extra files.<br />/SCopies subdirectories (excluding empty ones).<br />/ECopies all subdirectories (including empty ones).<br />/LEV:nCopies only the top n levels of the source directory tree.<br />/ZCopies files in restartable mode (that is, restarts the copy process from the point of failure).<br />/BCopies files in Backup mode (Backup copies are not restartable, but can copy some files that restartable mode cannot).<br />/ZBTries to copy files in restartable mode, but if that fails with an “Access Denied” error, switches automatically to Backup mode.<br />/COPY:copyflagsCopies the file information specified by copyflags, which can be any combination of the following <img src='/community/emoticons/emotion-2.gif' alt=':D' /> – file Data. S – file Security (NTFS ACLs).A – file Attributes. O – file Ownership information. T – file Timestamps. U – file aUditing infomation.Source and destination volumes must both be NTFS to copy Security, Ownership or Auditing information. If no this switch is omitted the default is /COPY<img src='/community/emoticons/emotion-2.gif' alt=':D' />AT, or /COPY<img src='/community/emoticons/emotion-2.gif' alt=':D' />ATS if /SEC is specified..<br />/COPYALLCopies Everything. Equivalent to /COPY<img src='/community/emoticons/emotion-2.gif' alt=':D' />ATSOU.<br /><br /><br />May the best cheaters win
  22. Twan New Member

    Robocopy doesn't prompt before overwriting, so there is no /Y

  23. Kringebob New Member

    I think I have this problem with xcopy.
    The sql job copying the files has been working fine for a few years. Now when it runs the files has not been copied to to the destination. When I tried to copy the file manually after the sql job has run I get a do you want to overwrite the file, if I say yes teh nthe file copies and is then visible.
    Help I'm so confused
  24. satya Moderator

    What is the version of SQL and windows used here?

Share This Page