Have you ever inherited a server and find out that your msdb database is rather large in nature?  Or maybe you have never set up any maintenance on your msdb with regards to retaining backup history? Okay, okay perhaps you don’t fall into those categories but maybe you have policies in place and you offline a database for whatever reason and your policies start to fail for backups not taken in the last 24 hours. Maybe you fall into one of these categories or maybe you fall into a different one, whatever the case may be there are options for removing the backup history cleanly and methodically without going all rogue style in SQL.

Microsoft has provided a couple system stored procedures within the msdb database for removing backup history, but I will only focus on one, sp_delete_database_backuphistory.

This little gem, if used properly, will remove the backup history for a particular database. This comes in very useful as recently taking a database offline to be decommissioned was messing with policy checks in place, particularly the one I mentioned above where a the policy checks to see if a database had been backed up within the last 24 hours.

The Code

The Tables Affected

• backupfile
• backupfilegroup
• backupmediafamily
• backupmediaset
• backupset
• restorefile
• restorefilegroup
• restorehistory

Conclusion

There are tools available at ones disposal to help in situations with SQL. Microsoft has provided a number of system stored procedures but it would not be prudent to just go start running scripts or executing procedures without first knowing what they are going to do to your systems. Never run anything in production or any environment for that matter without first testing it on your local sandbox.

Add this script to your repository, you never know when you will need it.