xcopy syntax error

Question

Post reply

xcopy syntax error

Denise McMillan

SSCertifiable

Points: 6216
More actions
February 19, 2007 at 2:44 pm

#116444

I'm trying to find a way to copy bak and trn files to another server aftera backup job is run. I keep having syntax error near \ message. This is what I've been playing around with:
xcopy \\HCLLPROD\D$\"Program Files"\"Microsoft SQL Server"\MSSQL\BACKUP\HCLL_Production_DB\*.* \\HCLLTEST\D$\HCLL\"PROD BACK"\*.* /d
Any ideas are greatly appreciated.

Viewing 15 posts - 1 through 15 (of 15 total)

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

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 1

Don't embed the quotes... put the quotes around each full path.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 2

I tried like this but still have invalid drive specification:

exec xp_cmdshell N'xcopy "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\HCLL_Production_DB\HCLL_Production_DB_db_200702182300.BAK" \\HCLLTEST\D$\HCLL\PRODBACKUP\HCLLProductionDBdb20070218.BAK'

If the syntax is ok, could this be a permission issue? The folder on the other server I'm trying to copy to (HCLLTEST) has D: as share drive and the D:\HCLL\PRODBACKUP\ file is able to share.

Thanks a lot.

steve goluchowski SSC Veteran Points: 280 More actions · Answer 3

Denise,

Try exec master.dbo.xp_cmdshell 'dir \\hclltest\d$\prodbackup\*.*' to see if you get any a listing of the files successfully.

~Steve

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 4

Thanks so much for the help so far. This command works when run in query analyser:

exec xp_cmdshell N'xcopy "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\HCLL_Production_DB\HCLL_Production_DB_db_200702182300.BAK" \\hclltest\prod_backup'

Now I'm trying to use it in a scheduled job and it doesn't give an error and says it runs successfully, but it doesn't actually do anything.

I want to run these steps, so it will remove the previous bak file and copy over the new daily bak file:

exec xp_cmdshell "del \\HCLLTEST\prod_backup\*.bak

exec xp_cmdshell N'xcopy "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\HCLL_Production_DB\HCLL_Production_DB_db_*.BAK" \\hclltest\prod_backup'

Again any help is greatly appreciated.

David Burrows SSC Guru Points: 65141 More actions · Answer 5

By default XCOPY will prompt to overwrite files and since prompts cannot be resolved in xp_cmdshell without using TYPE and redirector/pipe, I suspect that is why 'nothing happens'

Try adding /Y to XCOPY to suppress the prompt and overwrite, eg

exec xp_cmdshell N'xcopy /Y "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\HCLL_Production_DB\HCLL_Production_DB_db_*.BAK" \\hclltest\prod_backup\'

also add an additional backslash to the destination if using the above syntax to tell XCOPY that the destination is a directory

Far away is close at hand in the images of elsewhere.
Anon.

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 6

I tried running the job as suggested

exec xp_cmdshell N'xcopy /Y "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\HCLL_Production_DB\HCLL_Production_DB_db_*.BAK" \\hclltest\prod_backup\'

It ran successfully, but still didn't copy the file over to hclltest server, no error messages. If there is no error because the xp_cmdshell ran, is there a way to tell why it isn't copying?

Could it be access issues, I can copy when using query analyser, so is this some thing to do with sql agent running the job?

Thanks so much for your help!

David Burrows SSC Guru Points: 65141 More actions · Answer 7

Probably access permissions, 2 thing to try

1. Change job step to log information to a file and check the file

2. change the command to

exec xp_cmdshell N'xcopy /Y "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\HCLL_Production_DB\HCLL_Production_DB_db_*.BAK" c:\job.txt'">\\hclltest\prod_backup\ > c:\job.txt'

and providing access to the c: drive is ok check the job.txt file

Far away is close at hand in the images of elsewhere.
Anon.

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 8

I tried his and in the job.txt file is:

0 file(s) copied

Any other ideas?

David Burrows SSC Guru Points: 65141 More actions · Answer 9

Doh!! XCOPY errors are redirected to STDERR and wont appear in the job.txt file

Set the Job Step to log output and check the contents of that file

Far away is close at hand in the images of elsewhere.
Anon.

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 10

Well, I'm trying to teach myself all this, and it seems to take me forever!

I get for output: "Invalid drive specification {null}. The step succeeded."

when running:

exec xp_cmdshell N'xcopy /Y "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\HCLL_Production_DB_db_200702202300.BAK" \\hcll\prod_backup\ >c:\job.txt'

Thanks again for the help.

David Burrows SSC Guru Points: 65141 More actions · Answer 11

ah!

Just spotted something, you have a space in the output directory, you will need to put quotes around that as well

exec xp_cmdshell N'xcopy /Y "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\HCLL_Production_DB_db_200702202300.BAK" "\\hcll\prod_backup\"'

Far away is close at hand in the images of elsewhere.
Anon.

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 12

There is an underscore there, prod_backup. But I tried with quotes around as suggested and have same error. Thanks!

David Burrows SSC Guru Points: 65141 More actions · Answer 13

Only two things I can think of then

First, and most probable, your SQL Server is running under the 'Local System Account' instead of a Domain account. The Local System Account cannot access the network.

Second, either the server/pc hcll does not exist or the name cannot be resolved or the share prod_backup does not exist on that server/pc

Far away is close at hand in the images of elsewhere.
Anon.

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 14

SQL Server is running under the local system account. Do we need to change just the SQL Server Agent service to a domain acccount, or should w change the MSSQL Server service too? And thanks.