Log shipping via XCopy problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Log shipping via XCopy problem

Author

Topic

wiltech
Yak Posting Veteran

51 Posts

Posted - 2004-02-04 : 17:04:25

I have a SQL agent job setup to backup the transaction log and then copy it to another server. everything worked up until they decided to "retire" the NT4 server that I was copying the files to and replace it with a Windows 2003 server. The SQLServer and SQLAgent run under their own account.

The XCOPY command looks like:

EXEC master..xp_cmdshell 'xcopy "log_20040204_155718.TRN" "\\Server2k3\SQLLOGS\"'

The SQLAgent user account has full control of the share as well as the NTFS folder on the Windows 2003 server. Yet I get the following error:

Invalid drive specification
0 File(s) copied

I get the same error when I try to run that command in Query Analyzer. However if I log into the server using the SQLAgent user account and past the XCOPY command into a DOS windo it works fine.

Any thoughts or suggestions ?

Thanks
Tony W

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-04 : 17:06:31

Remove the double quotes around the file name and path information.

EXEC master..xp_cmdshell 'xcopy log_20040204_155718.TRN \\Server2k3\SQLLOGS\'

Does that work?

Tara

wiltech
Yak Posting Veteran

51 Posts

Posted - 2004-02-04 : 17:14:07

quote:
Originally posted by tduggan

Remove the double quotes around the file name and path information.

EXEC master..xp_cmdshell 'xcopy log_20040204_155718.TRN \\Server2k3\SQLLOGS\'

Does that work?

Tara

Thanks for the quick reply, I tried it and I'm still getting the same error.

Thanks
Tony W

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-04 : 17:17:31

What does this show:

EXEC master..xp_cmdshell 'dir \\Server2k3\SQLLOGS\*.*'

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-04 : 17:21:48

Does the sql server service account have permission on those directories?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

wiltech
Yak Posting Veteran

51 Posts

Posted - 2004-02-04 : 17:42:03

quote:
Originally posted by tduggan

What does this show:

EXEC master..xp_cmdshell 'dir \\Server2k3\SQLLOGS\*.*'

Tara

Well a different error:

Logon failure: unknown user name or bad password.

quote:
Originally posted by nr

Does the sql server service account have permission on those directories?

Yes it has full control of the NTFS folder as well as the share. I've also had it added to the Domain Admins group, as well as making it a Local Admin on the Win2003 server (it's not a domain controller)

Thanks
Tony W

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-04 : 17:45:10

When you run the command in Query Analyzer, it is using Query Analyzer account that you logged in with. This account doesn't have the correct permissions.

When you run the command as a job, it'll use the SQL Agent account.

So the service account works fine as you mentioned in the initial post, but the account you use in QA isn't working due to permissions.

Does xp_cmdshell work with:

xp_cmdshell 'dir C:\*.*'

I'm just trying to determine if the account can even run xp_cmdshell.

Tara

wiltech
Yak Posting Veteran

51 Posts

Posted - 2004-02-04 : 17:55:11

quote:
Originally posted by tduggan

When you run the command in Query Analyzer, it is using Query Analyzer account that you logged in with. This account doesn't have the correct permissions.

When you run the command as a job, it'll use the SQL Agent account.

So the service account works fine as you mentioned in the initial post, but the account you use in QA isn't working due to permissions.

I'm logged into the server using the SQLAgent accout, and using Windows Authentication to login to Query Analyzer.

The xcopy worked fine when it was copying to an NT4 server, that machine is now gone and a Windows 2003 server is in it's place. I have not been able to get xcopy to work, via xp_cmdshell to the 2003 server unless I paste it into a DOS window.

quote:

Does xp_cmdshell work with:

xp_cmdshell 'dir C:\*.*'

I'm just trying to determine if the account can even run xp_cmdshell.

Tara

Yes, I can get a directory listing of the C:\ as well as the C$ share on the local computer (Win2000 & SQL2000)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-04 : 18:00:44

So you can run dir \\Server2k3\SQLLOGS\*.* just fine in a cmd window using this account?

Can you get anything from the Windows 2003 server using xp_cmdshell?

Tara

wiltech
Yak Posting Veteran

51 Posts

Posted - 2004-02-04 : 18:04:30

quote:
Originally posted by tduggan

So you can run dir \\Server2k3\SQLLOGS\*.* just fine in a cmd window using this account?

Yes it works fin in a DOS command window

quote:

Can you get anything from the Windows 2003 server using xp_cmdshell?

Tara

Nope, nothing

Thanks
Tony W

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-04 : 18:11:36

It sounds like the credentials aren't being passed or aren't being passed correctly since you get a logon failure. Put the XCOPY command in a cmd file. Run the cmd file using a cmd window. Then run the cmd file using xp_cmdshell:

xp_cmdshell 'SomeFile.cmd'

Send the output of the cmd to a file:

xcopy log_20040204_155718.TRN \\Server2k3\SQLLOGS\ >C:\SomeDir\SomeOutputFile.txt

View the file after trying the cmd window and after the xp_cmdshell way. What do you see?

Tara

wiltech
Yak Posting Veteran

51 Posts

Posted - 2004-02-04 : 18:33:32

quote:
Originally posted by tduggan

It sounds like the credentials aren't being passed or aren't being passed correctly since you get a logon failure. Put the XCOPY command in a cmd file. Run the cmd file using a cmd window. Then run the cmd file using xp_cmdshell:

xp_cmdshell 'SomeFile.cmd'

Send the output of the cmd to a file:

xcopy log_20040204_155718.TRN \\Server2k3\SQLLOGS\ >C:\SomeDir\SomeOutputFile.txt

View the file after trying the cmd window and after the xp_cmdshell way. What do you see?

Tara

Tara,

When I run it in the command window, it works and outputs the file name to the text file, and 1 file copied

When I run it via xp_cmdshell it i get the same results as before ... same error in QA, and 0 files copied

I tried the with both xcopy and dir, same results.

Thanks for all your help and suggestions, I have to get out of here for the evening, I'll resume beating my head against this thing in the morning.

Thanks
Tony W

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-05 : 12:59:46

I'm out of ideas. I would suggest contacting MS at this point.

Tara

wiltech
Yak Posting Veteran

51 Posts

Posted - 2004-02-11 : 14:10:55

quote:
Originally posted by tduggan

I'm out of ideas. I would suggest contacting MS at this point.

Tara

Tara,

Thanks for all the help. Apparently I didn't wait long enough for the Active Directory permissions to propagate... as this started working on it's own @ 4:15pm on 02/08/2004 ...

Thanks
Tony W

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources