Inside SQL Server Backup and Restore History Pruning with sp_delete_backuphistory

I had a customer that was looking to document a restore that had occurred on one of their systems and didn’t see it. They had concerns about SQL Server accurately tracking history across time and noted they hadn’t cleaned any history.

We dug through some of their instance jobs and found one that ran sp_delete_backuphistory. The person didn’t realize this removes restore history as well. This post talks a bit about how this works.

The important thing to understand here is that this removes backup and restore history. Not just backups. I don’t know I like this, but it is what is documented (emphasis mine).

In this case, the sysadmin didn’t realize this removed restore entries. Once they did, they stopped worrying about things. We could have potentially restored an old backup of msdb and found this data, but they elected not to do this.

How The Procedure Works

We can actually see the code for this proc. I have expanded the msdb programmability section under system stored procedures.

I won’t show it, but this works in the following way:

1. create three table variables with a single ID column
2. insert data into these two tables from backupset where the date is older than the parameter passed in.
   1. backup_set_id from backupset
   2. media_set_id from backupset
3. insert data into the third table that matches the backup_set_id from the table in A
4. start a transaction
   1. delete from backupfile the matching backup_set_id values
   2. delete from backupfilegroup the matching backup_set_id values
   3. delete from restorefile the matching backup_set_id values
   4. delete from restorefilegroup the matching backup_set_id values
   5. delete from restorehistory the matching backup_set_id values
   6. delete from backupset the matching backup_set_id values
   7. delete from backupmediafamily where the media_set_id values match
   8. delete from backupmediaset where the media_set_id values match
5. commit the transaction (or rollback if errors).

This is a pretty simple flow, and it works well. The tricky part is that the is joins data in a way that makes sense, but might not be what you expect. This doesn’t remove restores based on the date, but based on the backup rows being removed.

Know Your Tools

This is a poorly named procedure, but that’s not an excuse for anyone. If you use this, and likely should, you need to ensure that you understand how it works. The phrasing in the documentation makes sense, but it can be a little misleading as many of us might assume the date is applied to backup and restore history tables.

It is not.