自動刪除備份和事務日誌會留下損壞的備份點
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搜尋了一下之後,我在測試數據庫上做了以下工作:
- 創建了手動完整備份,因為它是日誌傳送所必需的 - 這裡沒有什麼失敗的
- 使用刪除舊文件生成預設事務日誌傳送計劃任務。效果很好,每分鐘發送垃圾郵件 .trn 並從磁碟中刪除舊文件
- 設置維護計劃以在午夜為測試數據庫創建和刪除舊的完整備份 - 這裡也沒有問題
- 讓它從周五執行到週一,.trn 和 .bak 都被正確創建和刪除,所以我有兩天的備份可以玩:
- 嘗試從備份中恢復,這導致失去事務文件的問題。打開 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
以此類推,直到這一分鐘,總共有 29682 個備份點。
我希望“恢復”視窗再次響應,並且我有兩個測試數據庫可以自由測試。我可以安全地清除備份列表嗎?我怎樣才能讓它自動化,以很好地完成我目前的日程安排工作?
我在 db 和 os 上擁有完全的管理權限,完全的創意控制和 vm 上剩餘的大量資源,因此對可能的解決方案幾乎沒有任何限制。
可以使用sp_delete_backuphistory儲存過程清除較舊的歷史記錄。請注意,它不知道您是否刪除了什麼,但它會清除比您提供的日期 X 更早的歷史記錄。
一旦你清除了大量的備份條目,它應該會變得響應。通過以目前日期減去 X 天數作為作業的最後一步呼叫 SP,可以輕鬆地將其包裝到您的代理作業中。