從 SQL Server 中刪除文件

我一直在使用xp_delete_file刪除舊的備份文件，但我現在需要刪除屬於應用程序一部分的文件（作為 GDPR 的一部分）。由於xp_delete_file只允許您刪除日誌或備份文件，我想我會用 C# 編寫自己的。

我現在發現的問題是擴展儲存過程只能推廣到主數據庫。我寧願在我自己的使用者數據庫中包含這個新的儲存過程，這樣我就可以通過原始碼控制等進行分發。

有誰知道不使用 xp_cmdshell 的方法？

它可以在 PowerShell 中完成。我什至可以用 C# 編寫整個東西。然而，在 T-SQL 中也能做到這一點真的很方便。

我願意使用呼叫 C# 程式碼的 CLR 儲存過程。如果我無法部署儲存過程，我正在考慮將儲存過程更改為函式。

自 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選項，以便在傳入 a 時它甚至不會同時執行NULL（您可能應該向該方法添加額外的驗證，例如在傳遞空字元串時返回在等）：

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 或獨立使用）：

• SQLCLR 與 SQL Server 2017，第 2 部分：“CLR 嚴格的安全性” - 解決方案 1
• SQLCLR 與 SQL Server 2017，第 3 部分：“CLR 嚴格的安全性”——解決方案 2

還可以考慮支持我的非對稱密鑰建議，這將消除發布簽名程序集的大部分麻煩：

允許從二進制十六進製字節字元串創建非對稱密鑰，就像 CREATE CERTIFICATE

或者，如果您不想處理任何這些，SQL# SQLCLR 庫（我編寫的）中有一個File_Delete函式可以執行此操作。它甚至返回一個字元串，如果發生錯誤，這是錯誤消息，否則只是一個空字元串。但是，沒有辦法讓它在成功時傳回自定義字元串。另外，請注意，雖然有免費版，但文件系統功能僅在完整版中可用。但是安全方面都會得到干淨和正確的處理，甚至還有一個 T-SQL 儲存過程，它與 SQLCLR 程式碼存在於同一個數據庫中，它將設置和關聯Asymmetric Key``Login``[master]（這可以通過不需要完整安裝來幫助部署到新系統，只要在部署任何程序集之前部署和執行此儲存過程（假設 SQL Server 2017 的環境））。

更新

SQL Server 2019（在我發布此答案時不可用）引入了一個新的、未記錄的系統儲存過程，xp_delete_files它比xp_delete_file. 有關更多資訊，請在此處查看我的答案（也在 DBA.SE 上）：

新的系統儲存過程 sys.xp_delete_files 與 sys.xp_delete_file 有何不同？

引用自：https://dba.stackexchange.com/questions/198638