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;
Why isn’t this in a loop? Because I have a sinus infection and I didn’t feel like being that clever while I’m working from home. That’s why. You probably should be clever and put this in a loop. And put a nice little wait at the end of it that waits for a minute or two before repeating the whole thing. Me? I just sit here and zone out for a few minutes. The point is, this works.
If you’ll excuse me, I’m going to get back to watching day time TV and pressing F5 every 3 minutes.
This is Jeremiah
I live in Portland, OR. I have two dogs.
I recently received a Master's of Science in Computer Science from Portland State University.
I'm was Microsoft MVP from 2009 - 2018 with a pile of certifications. Somewhere along the way, I wrote a database client for Riak and then handed it off to the community.