Clearing out old backup history

Let’s say that you want to clear out your old backup history. Why would you want to do that? Let’s also assume that your maintenance plans (yes, maintenance plans) have not been cleaning up after themselves. If this has happened over a small period of time, you can just do this:``` EXEC sp_delete_backuphistory DATEADD(m, -1, GETDATE());

But what if you have a lot of backup history? Trying to delete two years of backup history will slow things down incredibly on your server and take a very very long time. To the point where your boss will come over and frantically say ‘Hey, why is the server refusing connections?’ Instead you’ll want to use something like this: DECLARE @oldest_date AS DATETIME;

IF OBJECT_ID(‘tempdb..#dates’) IS NOT NULL DROP TABLE #dates;

SELECT DISTINCT DATEADD(d, 0, DATEDIFF(d, 0, backup_start_date)) AS d INTO #dates FROM dbo.backupset

SELECT @oldest_date = DATEADD(d, 2, MIN(d)) FROM #dates WHERE d < DATEADD(mm, -1, GETDATE())

EXEC sp_delete_backuphistory @oldest_date;