Sql Server Backup to UNC

Question

I've create a maintenance plan on my SQL Server 2005 server. The backup should be written to another server. I'm using a UNC path for this. The user running the SQL Agent jobs has full access to the other server. It's admin on both servers.

The problem is that this statement fails ( has the correct server name ofcourse):

EXECUTE master.dbo.xp_create_subdir N'\\servername\c$\SqlServerBackup\Test'

The error I get is: Msg 22048, Level 16, State 1, Line 0 xp_create_subdir() returned error 123, 'The filename, directory name, or volume label syntax is incorrect.'

Does anyone know what could be the problem?

Answer 1

Is it not the lack of a double-backslash before the server name?

Answer 2

No, this website did not show the double backslah properly. I've corrected it now...

Answer 3

Check the he account SqlAgent is runs on. Usually it system\NetworkService ant it does not have access rights on other computers.

Either give temporarily write rights to everyone on the UNC path or in Control Panel/Services change the account used for SqlAgent.

Answer 4

I've backed-up up to UNC's mmany times. Make sure it's not a permissions issue w/ the account that sql server and sql agent are running as. I wouldn't back up to the hidden admin share like that perhaps it's the $ that's causing the problem. Create a share and backup to that. \server\share NOT \server\c$\folder

Answer 5

After having this problem myself, with none of the above solutions being clear enough, I thought I'd post a clearer response. The error is in fact nothing to do with syntax - it is entirely to do with permissions. The important thing here is that it is the SQL Server service account, NOT the SQL Server Agent account, that attempts to log in to create the directory. You can check your Event Viewer on the target server and look at the security log to see the failed logins from the SQL Server account on the other machine (mine was running as a local administrator).

To solve this, set your SQL Server service account to be a domain user that's allowed to write to the remote share. I would have expected SQL Server Agent to be able to use its own credentials to perform these backup operations, but apparently not!

Answer 6

Great answer. Helped me a lot.

Answer 7

SQL Agent does not actually run backups, it is capable of executing commands in SQL Server which performs the backup. Threfore, the SQL Server service account is the one that needs permissions to the UNC share.

However, I think there is an actual issue with the xp_create_subdir procedure. I am going to stop using maintenance plans because I get this exact same error every once in a while. No permissions are changing and it "usually" works, but "usually" doesn't cut it.