☰

バックアップとトランザクションログの自動削除により、壊れたバックアップポイントが残る

tag-icon • tag-icon sql-server backup-restoration ssms incremental-backup
バックアップとトランザクションログの自動削除により、壊れたバックアップポイントが残る

TLDR: デフォルトのログ配布ジョブとメンテナンス プランはディスクから古いファイルを削除しますが、バックアップは引き続き SSMS に表示されるため、「復元」ウィンドウの表示が非常に遅くなります。古い .trn/.bak を正しく削除するにはどうすればよいですか?

(Azure 仮想マシン、Windows Server 2016 Datacenter (10.0)、Microsoft SQL Server Web (2017 互換性レベル)、SMMS 17.3)

(表示される日付は、24 時間制の YYYY-MM-DD または DD-MM-YYYY 形式です)

データベースの管理は私のスキルセットにも職務内容にも含まれていませんが、MS Sql Server でバックアップを準備する任務を負ったため、この件に関しては素人として扱ってください。

私は、毎日完全なデータベース バックアップを作成し (もちろん外部ストレージにコピー)、利便性のためにそのうちのいくつかをサーバー上に保存し、古いものを削除してから、数日間、ポイントインデイ リストア用にトランザクション ログで穴を埋めることにしました。少し Google で検索した後、テスト データベースで次の操作を実行しました。

  1. ログ配布に必要なため、手動で完全バックアップを作成しました。ここでは失敗することはありません。

  2. 古いファイルを削除するデフォルトのトランザクション ログ送信スケジュール タスクを生成しました。正常に動作し、1 分ごとに .trn を送信し、ディスクから古いファイルを削除します。 ログ配布ジョブ

  3. メンテナンス プランを設定して、テスト データベースの古い完全バックアップを深夜に作成および削除します。ここでも問題はありません。

メンテナンス計画

  1. 金曜日から月曜日まで実行すると、.trn と .bak の両方が適切に作成および削除されたので、2 日間のバックアップができました。 ログ

  2. バックアップから復元しようとしましたが、トランザクション ファイルが見つからないという問題が発生しました。Tasks\Restore\Database を開くのに非常に時間がかかり、SMMS は数分間、空のデータベースの .trn を約 10 MB 処理するために 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 のバックアップ ポイントが発生しています。

「復元」ウィンドウが再び応答するようにしたいのですが、自由にテストできるテスト データベースが 2 つあります。バックアップ リストを安全にクリアできますか? 現在のスケジュール ジョブとうまく連携するように、これを自動化するにはどうすればよいですか?

私は DB と OS の両方で完全な管理権限を持ち、クリエイティブなコントロールを完全に持ち、VM に十分なリソースが残っているので、可能なソリューションに制限はほとんどありません。

答え1

使用することも可能ですsp_delete_backuphistory古い履歴をクリアするストアド プロシージャ。削除した内容は認識されませんが、指定した日付 X より古い履歴はクリアされます。

大量のバックアップ エントリをクリアすると、応答するようになります。ジョブの最後のステップとして、現在の日付から X 日数を引いた日付で SP を呼び出すことで、これをエージェント ジョブに簡単に組み込むことができます。

関連情報