主页 / dba / 问题 / 198638
Accepted
Andrew Lackenby
Asked: 2018-02-24 02:30:58 +0800 CST

从 SQL Server 中删除文件

  • 772

我一直在使用xp_delete_file删除旧备份文件，但我现在需要删除属于应用程序一部分的文件（作为 GDPR 的一部分）。由于xp_delete_file只会让你删除日志或备份文件，我想我会用 C# 编写自己的。

我现在发现的问题是扩展存储过程只能转出到主数据库。我宁愿将这个新的存储过程包含在我自己的用户数据库中，这样我就可以通过源代码管理等方式进行分发。

有谁知道不使用 xp_cmdshell 的方法吗？

它可以在 PowerShell 中完成。我什至可以用 C# 编写整个内容。然而，在 T-SQL 中也能做到这一点真的很方便。

我愿意使用调用 C# 代码的 CLR 存储过程。如果我不能部署存储过程，我正在考虑将存储过程更改为函数。

sql-server files

1 个回答

  1. Best Answer

    自 SQL Server 2005 发布以来，扩展存储过程 (XP) 已被弃用，新项目不应使用该 API。

    这可以通过 SQLCLR 轻松完成，是的，您可以从 C# 方法返回字符串（或大多数数据类型）。唯一限制返回 INT 的构造是存储过程，这对 T-SQL 和 SQLCLR 存储过程都适用。

    您将使用以下内容：

    using System;
using System.IO;

public class Stuff
{
  [SqlFunction(IsPrecise=true)]
  public static SqlString DeleteFile ([SqlFacet(MaxSize=500)] SqlString FilePath)
  {
    string _ReturnMessage = String.Empty;

    try
    {
      File.Delete(FilePath.Value);

      _ReturnMessage = "some text here";
    }
    catch (Exception _Error)
    {
      _ReturnMessage = _Error.Message;
    }
  }

  return new SqlString(_ReturnMessage);
}

    当您创建 T-SQL 包装器对象时，请务必指定RETURNS NULL ON NULL INPUT选项，这样即使NULL传入 a 也不会同时执行（您可能应该向该方法添加额外的验证，例如如果传入空字符串则返回在等）：

    CREATE FUNCTION dbo.DeleteFile
(
  @FilePath NVARCHAR(500)
)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME.... ;

    由于程序集需要标记为PERMISSION_SET = EXTERNAL_ACCESS，请不要将数据库设置为TRUSTWORTHY ON。相反，您应该签署程序集，在中创建非对称密钥或证书[master]，从该非对称密钥或证书创建登录名，然后授予该登录名EXTERNAL ACCESS ASSEMBLY权限（如果使用 SQL Server 2005 - 2016）或UNSAFE ASSEMBLY权限（从 SQL Server 2017 开始).

    有关通过构建过程执行此操作的详细说明，您可以尝试我在以下博客文章中描述的两种技术中的任何一种（都可以完全与 SSDT 一起使用或独立使用）：

    还要考虑支持我的非对称密钥建议，这将消除发布签名程序集的大部分麻烦：

    允许从二进制十六进制字节字符串创建非对称密钥，就像创建证书一样

    或者，如果您不想处理这些，SQL# SQLCLR 库（我编写的）中有一个File_Delete函数可以执行此操作。它甚至返回一个字符串，如果发生错误，这是错误消息，否则只是一个空字符串。但是，无法让它在成功时传回自定义字符串。另外请注意，虽然有免费版，但文件系统功能仅在完整版中可用。但是安全方面都将得到干净和正确的处理，甚至还有一个 T-SQL 存储过程，它与 SQLCLR 代码存在于同一个数据库中，它将在Asymmetric KeyLogin[master]（这可以通过不需要完整安装来帮助部署到新系统，只要在部署任何程序集之前部署和执行此存储过程（假设 SQL Server 2017 环境））。

    更新

    SQL Server 2019（在我发布此答案时不可用）引入了一个新的、未记录的系统存储过程，xp_delete_files它比xp_delete_file. 有关更多信息，请在此处查看我的回答（也在 DBA.SE 上）：

    新系统存储过程sys.xp_delete_files与sys.xp_delete_file有何不同？

    • 1

相关问题