DeletePersonHave 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

/*This code deletes backup history for a specific database*/
use msdb
go
exec sp_delete_database_backuphistory ‘yourdatabasename’
go

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s