SQL Server Performance Forum – Threads Archive

xcopy not working

EXEC master..xp_cmdshell ‘xcopy "D

rogram 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?

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
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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 Cheers
Twan

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

rogram FilesMicrosoft SQL ServerMSSQLBACKUPDB_backup_device.bak" "\SecondServerShare" /C /Y or xcopy "D

rogram 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.

Correction from Command line when I execute xcopy "D

rogram 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.

Post the code used in the job, for the syntax information run XCOPY /? on command line (ages ago I’ve used xcopy). Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I have tried different way in sql agent here are some examples
These two give me invalid drive specification.
xcopy "D

rogram FilesMicrosoft SQL ServerMSSQLBACKUPDB_backup_device.txt" \IPAddressShare/C /Y xcopy "D

rogram 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

rogram 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.

What your security model? Brett :cool:

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.

Check any firewall is involved or monitor from network side. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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?

Hi Robocopy is a win32 which is a ultility provided by microsoft seem to be a little better than xcopy http://www.microsoft.com/downloads/…69-57ff-4ae7-96ee-b18c4790cffd&displaylang=en that is the link for Windows Server 2003 Resource Tools Kit which has Robocopy and a lots of other thing work with all windows version try it May the best cheaters win

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

ath or \serversharepath) net stop MSSQLServer
robocopy —–all the database—-
net start MSSQLServer
May the best cheaters win

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??

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 Cheers
Twan

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

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

try to use ROBOCOPY in place of your XCOPY I am pretty sure it work May the best cheaters win

http://www.xxcopy.com/xxcopy30.htm http://www.ss64.com/nt/robocopy.html I was reading up on these, by the way what is the equevelent of the /Y switch in ROBOCOPY?

/MIR make the exact image directories etc and override the destination with source May the best cheaters win

The list in under C

rogram FilesWindows Resource KitsTools
obocopy.doc when you install the kits SwitchFunction Performed /XLExcludes Lonely files and directories. /ITIncludes Tweaked files. /ISIncludes Same files. /XCExcludes Changed files. /XNExcludes Newer files. /XOExcludes Older files. /XX Suppresses reporting and processing of Extra files. /SCopies subdirectories (excluding empty ones). /ECopies all subdirectories (including empty ones). /LEV:nCopies only the top n levels of the source directory tree. /ZCopies files in restartable mode (that is, restarts the copy process from the point of failure). /BCopies files in Backup mode (Backup copies are not restartable, but can copy some files that restartable mode cannot). /ZBTries to copy files in restartable mode, but if that fails with an â€œAccess Deniedâ€ error, switches automatically to Backup mode. /COPY:copyflagsCopies the file information specified by copyflags, which can be any combination of the following <img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ /> â€“ 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=’

‘ />AT, or /COPY<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />ATS if /SEC is specified.. /COPYALLCopies Everything. Equivalent to /COPY<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />ATSOU. May the best cheaters win

Robocopy doesn’t prompt before overwriting, so there is no /Y Cheers
Twan

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

What is the version of SQL and windows used here?

]]>