Sql-Server

自動刪除備份和事務日誌會留下損壞的備份點

  • March 15, 2018

TLDR:預設日誌傳送作業和維護計劃正在從磁碟中刪除舊文件,但備份仍顯示在 SSMS 中,因此顯示“恢復”視窗非常慢。如何正確刪除舊的 .trn/.bak?


(Azure 虛擬機、Windows Server 2016 Datacenter (10.0)、Microsoft SQL Server Web(2017 兼容級別)、SMMS 17.3)

(顯示日期為 YYYY-MM-DD 或 DD-MM-YYYY 格式,24 小時制)

管理數據庫既不是我的技能,也不是我的工作描述,但我的任務是在 MS Sql Server 上準備備份,所以請把我當作這個主題的外行。

我決定每天創建完整的數據庫備份(當然是複製到外部儲存),為了方便起見,在伺服器上保留一些備份,刪除舊備份,然後用事務日誌填充漏洞,以便在幾天內進行日點恢復。在Google搜尋了一下之後,我在測試數據庫上做了以下工作:

  1. 創建了手動完整備份,因為它是日誌傳送所必需的 - 這裡沒有什麼失敗的
  2. 使用刪除舊文件生成預設事務日誌傳送計劃任務。效果很好,每分鐘發送垃圾郵件 .trn 並從磁碟中刪除舊文件 日誌傳送作業
  3. 設置維護計劃以在午夜為測試數據庫創建和刪除舊的完整備份 - 這裡也沒有問題

維護計劃

  1. 讓它從周五執行到週一,.trn 和 .bak 都被正確創建和刪除,所以我有兩天的備份可以玩: 日誌
  2. 嘗試從備份中恢復,這導致失去事務文件的問題。打開 Tasks\Restore\Database 非常非常慢,SMMS 正在為大約 10MB 的 .trn 空數據庫研磨 100% 磁碟幾分鐘。對於沒有自動完整備份的數據庫,它列出了所有 .trn 曾經創建的文件(首先是手動完整備份),顯然伴隨著“找不到文件”錯誤”。對於每日完整備份的數據庫,預設情況下,有限的結果集返回到上次完整備份和還原工作正常,但僅顯示備份列表仍然很慢。瀏覽時間線也很慢(幾分鐘“沒有響應”)。

我在https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/找到了獲取腳本歷史記錄的腳本(稍作修改以返回所有感興趣的數據庫的備份)

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE 
--(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
--and
[database_name] = 'test'
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date 

它包括已刪除的 .trn 和 .bak 文件: 在此處輸入圖像描述

以此類推,直到這一分鐘,總共有 29682 個備份點。

我希望“恢復”視窗再次響應,並且我有兩個測試數據庫可以自由測試。我可以安全地清除備份列表嗎?我怎樣才能讓它自動化,以很好地完成我目前的日程安排工作?

我在 db 和 os 上擁有完全的管理權限,完全的創意控制和 vm 上剩餘的大量資源,因此對可能的解決方案幾乎沒有任何限制。

可以使用sp_delete_backuphistory儲存過程清除較舊的歷史記錄。請注意,它不知道您是否刪除了什麼,但它會清除比您提供的日期 X 更早的歷史記錄。

一旦你清除了大量的備份條目,它應該會變得響應。通過以目前日期減去 X 天數作為作業的最後一步呼叫 SP,可以輕鬆地將其包裝到您的代理作業中。

引用自:https://serverfault.com/questions/900862