SQL Server 維護計劃 - 刪除舊備份的清理任務的行為是什麼

這似乎很明顯，但是要找到解決此問題的實際文件非常困難。

場景：

• 完全備份 - 每週（設置為 7 天后過期）
• 差異備份 - 每日（設置為 7 天后過期）
• 事務日誌備份 - 每小時（設置為 3 天后過期）

在這種情況下，有一個完整備份，然後有 6 天的差異備份與完整備份相關聯，中間有幾個事務日誌。

現在，我想創建一個清理任務來刪除舊備份。如果我將清理任務設置為刪除超過 7 天的備份文件 (bak)，它是否會刪除完整備份並基本上孤立 6 個差異備份？

備份任務的過期策略是否“鎖定”這些文件在過期之前不被刪除？本質上，我可以將清理任務設置為刪除超過 1 天的文件，但不刪除尚未過期的備份文件嗎？

這裡的目標是做幾件事：

• 如果刪除了 FULL 備份，那麼所有差異都是無用的，因此也應該刪除它們。
• 至少保留一整週的完整備份。即如果我告訴清理任務（在第 8 天）在 7 天后刪除文件並且它刪除了 FULL，那麼剩餘 6 天的差異將變得無效，現在我只有 1 天的備份。

我想以同樣的方式處理事務日誌，但我只想要 3 天的時間點恢復。之後，預計將回退到最新的、合適的不帶事務日誌的差異備份。所以，我也會刪除超過 3 天的事務日誌。

備份任務的過期策略是否“鎖定”這些文件在過期之前不被刪除？

否。該Backup set will expire設置指示備份集何時可以被另一個備份覆蓋。它不會導致文件被刪除，也不會阻止文件被刪除。

本質上，我可以將清理任務設置為刪除超過 1 天的文件，但不刪除尚未過期的備份文件嗎？

不會。清理任務將刪除舊文件。這些文件中的任何備份集都將消失，無論它們的到期設置如何。

如果刪除了 FULL 備份，那麼所有差異都是無用的，因此也應該刪除它們。

您是正確的，差異備份是無用的。但是刪除完整備份的清理任務不知道這一點，所以它沒有辦法刪除差異。您只需要等到清理任務下一次執行，下一次，下一次，它最終會刪除差異備份，一次一個，因為它們達到截止年齡（7天，在你的例子中）。

清理任務只是針對文件列表執行一個名為 xp_delete_file 的擴展儲存過程。此過程檢查提供的文件以驗證它是 SQL Server 備份文件還是 SQL Server 日誌文件。如果是，並且它早於提供的日期，則刪除該文件。

對於像您這樣的自定義解決方案，您應該使用 PowerShell 提取滿足您要求的備份文件列表，然後使用 Remove-Item 將其刪除。像下面這樣的東西應該​​可以工作。

它查詢 msdb 中的備份歷史記錄表，然後使用返回的備份列表進入循環。循環測試文件是否存在，如果存在則刪除文件。

$SQLScript = "DECLARE @DaysToKeep INT = 7,
   @LogDaysToKeep INT = 3

;WITH FullBackup AS (
   SELECT bs.database_name,
       bs.backup_start_date,
       bmf.physical_device_name,
       bs.backup_set_id,
       bs.compressed_backup_size,
       bs.first_lsn,
       'Full' AS [Type]
   FROM msdb..backupset bs WITH (NOLOCK)
   INNER JOIN msdb..backupmediafamily bmf WITH (NOLOCK) ON bmf.media_set_id = bs.media_set_id
   WHERE bs.[type] = 'D'
       AND bs.backup_start_date < DATEADD(DAY, -@DaysToKeep, GETDATE())
), DiffBackup AS (
   SELECT bs.database_name,
       bs.backup_start_date,
       bmf.physical_device_name,
       bs.backup_set_id,
       bs.compressed_backup_size,
       'Diff' AS [Type]
   FROM msdb..backupset bs WITH (NOLOCK)
   INNER JOIN msdb..backupmediafamily bmf WITH (NOLOCK) ON bmf.media_set_id = bs.media_set_id
   LEFT JOIN FullBackup fb ON fb.database_name = bs.database_name AND bs.database_name = fb.database_name AND bs.database_backup_lsn = fb.first_lsn
   WHERE bs.[type] = 'I'
       AND bs.backup_start_date >= fb.backup_start_date
), LogBackups AS (
   SELECT bs.database_name,
       bs.backup_start_date,
       bmf.physical_device_name,
       bs.backup_set_id,
       bs.compressed_backup_size,
       'Log' AS [Type]
   FROM msdb..backupset bs WITH (NOLOCK)
   INNER JOIN msdb..backupmediafamily bmf WITH (NOLOCK) ON bmf.media_set_id = bs.media_set_id
   LEFT JOIN FullBackup fb ON fb.database_name = bs.database_name AND bs.database_name = fb.database_name AND bs.database_backup_lsn = fb.first_lsn
   LEFT JOIN DiffBackup dd ON dd.database_name = bs.database_name
   WHERE bs.[type] = 'L'
       AND bs.backup_start_date >= fb.backup_start_date
       AND bs.backup_start_date <= DATEADD(DAY, -@LogDaysToKeep, GETDATE())
   ), Backups AS (
   SELECT database_name,
       physical_device_name,
       [type],
       compressed_backup_size,
       backup_start_date
   FROM FullBackup
   UNION
   SELECT database_name,
       physical_device_name,
       [type],
       compressed_backup_size,
       backup_start_date
   FROM DiffBackup
   UNION
   SELECT database_name,
       physical_device_name,
       [type],
       compressed_backup_size,
       backup_start_date
   FROM LogBackups
)

SELECT b.database_name,
   b.[type],
   b.backup_start_date,
   b.physical_device_name
FROM Backups b"

$BackupFiles = Invoke-Sqlcmd -ServerInstance localhost -Database msdb -Query $SQLScript

foreach ($File in $BackupFiles)
{
   if (Test-Path $File.physical_device_name)
   {
       Write-Output "Remove file $($File.physical_device_name)..."
       Remove-Item -Path $File.physical_device_name
   }
   else
   {
       Write-Output "File $($File.physical_device_name) no longer exists. Skipping."
   }
}

將此保存為 SQL Server 上的腳本文件，並通過 SQL Server 代理作業呼叫它，以根據您的要求自動進行清理。

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