SQL Server的xp_delete_file无法删除文件

Question

我正在尝试编写一些SQL，以删除类型为“.7z”的文件，这些文件的时间超过7天。

以下是我目前有的但无法正常运行的代码：

DECLARE @DateString CHAR(8)
SET @DateString = CONVERT(CHAR(8), DATEADD(d, -7, GETDATE()), 1)
EXECUTE master.dbo.xp_delete_file 0, 
                  N'e:\Database Backups',N'7z', @DateString, 1

我也试过将末尾的'1'改成'0'。

这会返回“成功”，但文件并未被删除。

我正在使用带有SP2的标准SQL Server 2005版本。

- Gern Blandston

Answer 1

遇到了类似的问题，找到了各种答案。这是我找到的。

你不能使用xp_delete_file删除7z文件。这是一个未记录的扩展存储过程，是SQL 2000的遗留物。它检查要删除的文件的第一行，以验证它是SQL备份文件还是SQL报告文件。它不基于文件扩展名进行检查。据我所知，它的预期用途是在维护计划中清理旧备份和计划报告。

以下是基于Tomalak链接的示例，用于删除7天前的备份文件。让人困惑的是'sys'模式、文件夹路径中的尾随斜杠以及没有点来查找文件扩展名。SQL Server运行的用户还需要在文件夹上拥有删除权限。

DECLARE @DeleteDate datetime
SET @DeleteDate = DateAdd(day, -7, GetDate())

EXECUTE master.sys.xp_delete_file
0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
N'D:\SQLbackups\', -- folder path (trailing slash)
N'bak', -- file extension which needs to be deleted (no dot)
@DeleteDate, -- date prior which to delete
1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)

请注意，SP2 中的 xp_delete_file 已经崩溃，无法在报告文件中使用；在 [http://support.microsoft.com/kb/938085] 上有一个热补丁。我还没有在 SP3 中测试过它。

由于它是未记录的，xp_delete_file 可能会在未来版本的 SQL Server 中消失或更改。许多站点建议使用 shell 脚本来进行删除。

-

---

“(无点)”的文件扩展名对我很有帮助。谢谢！ - patrickbadley

Answer 2

据我所知，xp_delete_file 只能删除 SQL Server 2005 可识别的文件（备份文件、事务日志等）。也许你可以尝试这样做：

xp_cmdshell 'del <filename>'

请注意，此命令将通过 xp_cmdshell 调用操作系统的 del 命令来删除指定的文件。

- smink

---

smink：我尝试将文件扩展名更改为.bak，但没有成功。它是否会查看文件以查看我是否在搞砸它，而不仅仅是查看扩展名？ - Chris Burgess

是的，它会检查文件的二进制签名。 - Jorge Ferreira

我尝试使用一个 .bak 的 SQL 备份文件，但它并没有删除它。 - Chris Burgess

Answer 3

此存储过程仅会删除本地 SQL Server 备份文件或本地维护报告文件（出于安全考虑）。

如 Smink 建议的，您可以使用：

xp_cmdshell 'del <filename>'

在文件夹上设置适当的权限。

- Eduardo Molteni

Answer 4

我阅读了多个人在解决扩展存储过程xp_delete问题时采用的不同方法和解决方案。这些解决方案如下：

1. 在配置SSIS维护任务时，确保文件扩展名中没有句号（.）。
2. 对于每个数据库备份，请务必单击包含第一级子文件夹（如果存在）。
3. 请务必单击顶部的备份文件。维护任务会检查文件类型。对于数据库备份，我认为它会检查备份文件头。

在我的情况下，上述所有内容都是正确的。网上有一些评论称例程xp_delete有缺陷。

当备份文件未被删除时，我提取了维护SQL并从SSMS运行它。结果显示该文件不是SQL Server备份文件。这条消息是错误的，因为备份可以成功恢复，从而得到一个操作数据库。

用于验证数据库的命令如下：

RESTORE HEADERONLY FROM DISK = N'<file path\filename>.Bak'
RESTORE VERIFYONLY FROM DISK = N'<file path\filename>.bak'

以上两个命令都表明备份文件是有效的。

接下来我打开了事件查看器，发现有关连接管理器的登录错误消息。这很奇怪，因为我已经使用测试连接按钮验证了连接。这些错误与我创建的任何帐户都无关。

事件查看器消息：

*源“MS SQL SERVER”的事件ID 17052的描述找不到。可能是引发此事件的组件未安装在本地计算机上，或者安装已损坏。您可以在本地计算机上安装或修复组件。 如果事件起源于另一台计算机，则必须保存显示信息并保存事件。

事件包含以下信息：

严重性：16 错误：18456，操作系统：18456 [Microsoft][SQL Server Native Client 11.0][SQL Server]用户'domain\servername$'的登录失败。*

接下来，我登录了一个xp_delete正常运行的机器。在查看了活动目录并没有找到该系统帐户后，我继续前往事件查看器查找类似的消息。在这里，很明显domain\server$的帐户被映射到系统安全。

下一步是比较xp_delete正常工作的数据库安全性和它不工作的数据库安全性。在xp_delete不起作用的数据库中，安全性中缺少2个登录。这两个缺少的登录是： NT AUTHORITY\SYSTEM NT Service\MSSQLSERVER

添加了NT service\MSSQLSERVER后，xp_delete成功工作。

测试的一种方法是使用维护任务删除单个文件。

- user5923365

---

好的，一些顾问在我们公司配置了这个，显然他们将扩展名设置为“.bak”。将句点删除并改为“bak”后，它可以正常工作。谢谢。 - Mark

Answer 5

我发现了这个问题，但是解决方案对我不适用（因为它是SQL Server本身作为维护计划的一部分创建的.bak文件）。

在我的情况下，问题出在安全性上。脚本是以启动SQL Server（MSSQL）的用户身份运行的（在我的情况下，可能是大多数情况下的“网络服务”），但是该用户没有访问尝试删除文件的文件夹的权限。

因此，添加“网络服务”并授予“修改”权限有所帮助。

- Holger

Answer 6

我知道这可能有点过时，但我想与大家分享我的挫败感。我遇到了很多帖子中提到的同样的问题，但似乎没有什么方法可以解决。然后我想起我们的数据库上有一个名为NetLib的加密层。这意味着备份是加密的，因此xp_delete_file无法读取头文件。现在我在操作系统中使用批处理文件，并从代理作业中调用它。希望这能帮助到某些人。

- dwjv

Answer 7

尝试将第一个参数从0改为1。

这里是我刚发现的关于xp_delete_file的小摘要。听起来像是你无法使用此过程。

- Tomalak

---

我在阅读我刚发布的摘要后明白了。某个误导性的论坛帖子指向了我的第一个答案方向。 - Tomalak

Answer 8

在我的情况下，由于某种原因，帐户NT Service\MSSQLSERVER没有sysadmin权限。在安装SQL Server时，默认会添加该权限。我将sysadmin权限添加到了NT Service\MSSQLSERVER帐户中，然后xp_delete_file函数就可以正常工作了。

- Frank Robinson

Answer 9

通常情况下，当您将数据库移动到另一台服务器或在同一台服务器上重新安装SQL实例但备份留在旧目录中时，我们会遇到这种情况。

例如： 您将数据库从server1移动到server2，但是您有一个维护计划的服务器，该计划执行定期备份，或者您在server1上重新安装SQL实例并还原数据库。

在备份情况下，保存为msdb中信息的集不再存在，因此，由备份集表导出的故障未检查任何信息，因此所有已创建的旧备份都不会被删除。

EXECUTE master.sys.xp_delete_file  0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)

第一个参数显示正在使用 msdb 中的表。

希望这能帮助到某些人。

- Jivomir Yovkov