SQL Server xp_delete_file 不删除文件 [英] SQL Server xp_delete_file not deleting files

问题描述

我正在尝试编写一些 SQL 来删除超过 7 天的.7z"类型的文件.

I'm trying to write some SQL that will delete files of type '.7z' that are older than 7 days.

以下是我无法使用的:

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

I've also tried changing the '1' a the end to a '0'.

这会返回成功"，但文件不会被删除.

This returns 'success', but the files aren't getting deleted.

我使用的是 SQL Server 2005，标准版，带 SP2

I'm using SQL Server 2005, Standard, w/SP2

推荐答案

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

Had a similar problem, found various answers. Here's what I found.

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

You can't delete 7z files with xp_delete_file. This is an undocumented extended stored procedure that's a holdover from SQL 2000. It checks the first line of the file to be deleted to verify that it is either a SQL backup file or a SQL report file. It doesn't check based on the file extension. From what I gather its intended use is in maintenance plans to cleanup old backups and plan reports.

这是一个基于 Tomalak 链接的示例，用于删除超过 7 天的备份文件.让人们感到不安的是sys"模式、文件夹路径中的尾部斜杠，以及文件扩展名中没有要查找的点.运行 SQL Server 的用户也需要对该文件夹具有删除权限.

Here's a sample based on Tomalak's link to delete backup files older than 7 days. What trips people up is the 'sys' schema, the trailing slash in the folder path, and no dot in the file extension to look for. The user that SQL Server runs as also needs to have delete permissions on the folder.

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)

请注意，xp_delete_file 在 SP2 中已损坏，无法处理报告文件；在 [http://support.microsoft.com/kb/938085].我还没有用 SP3 测试过它.

Note that xp_delete_file is broken in SP2 and won't work on report files; there's a hotfix for it at [http://support.microsoft.com/kb/938085]. I have not tested it with SP3.

由于它未记录在案，因此 xp_delete_file 可能会在 SQL Server 的未来版本中消失或更改.许多站点推荐使用 shell 脚本来代替执行删除操作.

Since it's undocumented, xp_delete_file may go away or change in future versions of SQL Server. Many sites recommend a shell script to do the deletions instead.

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