SQL Server备份到UNC [英] Sql Server Backup to UNC

问题描述

我已经在SQL Server 2005服务器上创建了一个维护计划.备份应写入另一台服务器.我为此使用UNC路径.运行SQL Agent作业的用户对另一台服务器具有完全访问权限.这是两台服务器上的管理员.

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):

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

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

我得到的错误是: 消息22048，级别16，状态1，第0行 xp_create_subdir()返回错误123，文件名，目录名或卷标签语法不正确."

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?

推荐答案

我自己遇到了这个问题，并且上述解决方案都不是很清楚，我想我会发布一个更清晰的答复.该错误实际上与语法无关-完全与权限有关.这里重要的是尝试登录以创建目录的是SQL Server服务帐户而不是SQL Server代理帐户.您可以在目标服务器上检查事件查看器，并查看安全日志，以查看另一台计算机(我是作为本地管理员运行的)上来自SQL Server帐户的登录失败.

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

要解决此问题，请将您的SQL Server服务帐户设置为允许写入远程共享的域用户.我希望SQL Server代理能够使用其自己的凭据来执行这些备份操作，但是显然不能！

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!

这篇关于SQL Server备份到UNC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！