From looking around at some of the Issues that get logged for dbatools, it looks like people never clear out their SQL backup history from msdb. This causes slow queries and potentially misleading backup trees to be returned.
In my (not so humble) opinion you really shouldn’t let your backup history fill up with, let’s be honest, useless junk. In this post I’ll go through why I think you should be keeping way less in there than people think you should
It’s not an audit trail
A common reason people give for keeping years worth of backup history records is that they need it to show the auditors. I’ve yet to find an auditor who wants a raw SQL database of database to look at.
The options I’ve used in the past are to use:
- Export it to Excel or CSV on a regular basis. Easier to hand off to an auditor, compresses well and get’s it out of your database
- Monitoring system records. You are monitoring your backups? In that case, you should have record of no failed jobs.
Please check what your auditors are actually asking for and why. Unless they’re also asking for verification and restore test results pure backup history is worthless. Because as a number of people have said over the years (in various ways):
An untested backup is just a wish that may not be granted
Me, probably
Will you ever go that far back?
So you’ve got 2 years worth of SQL Server backup history tucked away in msdb. Now, be honest, are you ever going to use it? Really, you’re going to roll a database back 6 months? Why?
That would be a hell of a lot of data to throw away to /dev/null or have to import all over again. Would there ever be a business case for doing that?
And once you’ve restored it, that leads on to…..
Once it’s restored, it’s a new database
You restore a SQL Server database and you start 2 new things:
- A new Recovery Fork
- A new LSN chain
All databases are running along a Recovery Fork (identified by a RecoveryForkGUID), even a new one will have one. You perform a restore, and SQL Server will generate a new Recovery Fork under the covers. Much like Ghostbusters, SQL Server won’t let you cross streams
As covered elsewhere on here, SQL Server uses LSNs (Log Sequence Numbers) to track database actions, and as backups are database actions they are also linked to LSNs. You’ll be taking the database back to an LSN in the past, and restarting the count from there. This is why SQL Server uses the recovery fork. Even if you were lucky enough to have backups starting on the same LSN (and it’s the sort of luck where I’d be asking you for the lottery numbers) you still won’t be able to use the backup.
So if all the backups are still in the backup history they’re useless, and potentially very confusing when you come to do a restore
Can you even restore it?
If you’re storing 2 years of old backup history, are you also storing the old backups as well? If you don’t have the backups to restore, again the history is worthless and might as well be deleted
I’m pretty hot on backups, but even I don’t keep things that long. Currently we’ll keep everything for 2 weeks, then we’ll get rid of Transaction Log and Differential backups. After 4 weeks we’ll only keep the Full backup from the last day of the month, and then at the end of the year we’ll only keep the last Full backup
So again, why keep the history if I can’t use it to restore most of the databases it contains?
Do you need that granularity?
As mentioned in the last reason you may end up with just a few backups kicking around that may need restoring for investigations or legal reasons. Do you need the backup history to restore those?
With tools like Restore-DbaDatabase I’d say no. While generating the restore chain from a folder full of backups may take a while, for a single database backup you’re only looking at seconds to scan the file(s) and be restoring. Probably quicker than trying to find the records in a bloated backup history table 😉
So what do I suggest
I’d suggest really looking at why you’re storing all that probably useless backup history.
Personally I consider anything more that a months worth as being too much, you (and your boss) may think that’s a bit tight. But I can’t ever see us needing to roll backup to a point in time over a month ago. Most of the times we do need to, the end of the month will be good enough.
So, to clear down the backup history, schedule sp_delete_backuphistory to run once a week like so:
use msdb
go
declare @oldest DATETIME = Getdate()-28;
exec sp_delete_backuphistory @oldest_date=@oldest;
This will get rid of ALL backup history over 4 weeks old from ALL databases. This will work on SQL Server from 2005 upwards
Or you can enable the ‘History Cleanup Task’ in your maintenance plans.
Or if you’re using Ola’s great SQL Server maintenance scripts they’ll install a sp_delete_backuphistory job for you
So, go on. Get those deletions automated and make your life easier 🙂
Feedback
Obviously, all of the above is just my opinion and thoughts on best practice I’ve picked up over the years. You may have a need to keep years of backup history, and I’d love to hear what it is.
Ben Thul
One word of caution regarding cleaning up old history. The SQL that you have posted works well for keeping an already trim data set in fighting shape. But what if it was allowed to get large? sp_delete_backuphistory isn’t very smart under the hood, so if you have 2y worth of backup history and you want to get it down to 28d worth, calling the above will try to delete everything in one go.
Luckily for us, it’s fairly easy to work around.
declare @retain_days smallint = 28;
declare @earliest datetime = (
select min(backup_start_date)
from msdb.dbo.backupset
),
@retain_date datetime = dateadd(day, -@retain_days, getdate());
if (@earliest is not null)
begin
while(@earliest < @retain_date)
begin
set @earliest = dateadd(day, 1, @earliest);
exec msdb.dbo.sp_delete_backuphistory @oldest_date=@earliest;
end
end
Stuart Moore
Good catch, thank you for the query.
Another reason to keep on top of backup history from the start, it’s a pain to clean down at a later date.