February 11, 2011 at 9:22 am
I am having a problem with backing up our database on our SQL 2008 server.
The backup itself runs fine.
My goal is to make a backup of the database locally on the server. Once the backup completes, I want to robocopy the .bak file to a file server that gets backed up nightly.
The copy to the file server is where I am running into issues.
My SQL Server service and SQL Server Agent service are both running under domain accounts (sqlserver@mydomain.com). The file server I am trying to copy to is also the domain controller for mydomain.com.
I have a job... basically I back the db up locally (@BackupLocation = 'C:\SQL Backups\My DB'
-- backup the database to the local machine
BACKUP DATABASE @DatabaseName
TO DISK = @BackupLocation
WITH FORMAT -- format overwrites any previous backups
This works fine. Then I try to ROBOCOPY it...
My robocopy command looks like this.. (I execute it with exec master..xp_cmdshell)
ROBOCOPY "C:\SQL BACKUPS\MY DB" "\\server\general\sql backups\my db" MyDB.bak /COPYALL /V /LOG+:"C:\SQL BACKUPS\MyDBRobocopy.LOG"
In my robocopy log file I get this:
ERROR 1314 (0x00000522) Copying NTFS Security to Destination Directory C:\SQL BACKUPS\MY DB\
A required privilege is not held by the client.
I gave the domain user that the SQL Agent and Server services are running under full control on this folder...
Any suggestions?
thanks
sb
February 11, 2011 at 9:28 am
Two possibilities I can think of here:
Check permissions at both ends. You might have full permissions on the source, but not on the destination, or vice versa. Seems you've covered this, but double check both ends.
Possibility of a permissions-hop issue. Doesn't seem likely, but is the job being called on one machine, which connects to another, which then has to connect to a third?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2011 at 10:43 am
Thank you for the reply..
I checked the effective permissions on the source and destination folder - both have full control.
There are only 2 machines involved in this process...
Strange thing is - If I copy the command that the TSQL Script runs, and paste it in a command prompt - it runs fine. Obviously - this is running under my account.....
Maybe I will try logging on to that server as the domain user that the service is running under and try that same approach...
Thanks
sb
February 11, 2011 at 10:57 am
If you are copying a file to a share, the service account must have permissions to both the share and the underlying directory structure.
February 11, 2011 at 10:59 am
Yeah, if it works when you run it, and not when the service account runs it, it's definitely a permissions issue. Do you have a domain admin you can ask for help on that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2011 at 11:13 am
@SSCrazy -- when you say the service account - you mean simply - the account the service is running under?
When the robocopy runs, and copies to the file server - does it logon to the file server as a service or no - just a network logon?
@GSquared -- unfortunately - I am he 🙂
I did just verify again - the effective permissions on both source and destination, my mydomain\sqlserver user has full control for NTFS permissions.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply