Cleaning Backup and Restore History

Every time you do backup and/or restore on your SQL server, entries are made to several tables in the msdb database. These things can really add up really quickly, especially if you do a lot of backups (like backing up the transaction log every 15 minutes) or restores (hopefully those restores were done on your development servers, instead of production servers). Obviously, this will cause your msdb database to grow over time. In addition, overtime, if you are using backup/restore UI on SQL Server Management Studio, you might notice slower performance when trying to retrieve the backup history.

By default, SQL Server would maintain the backup and restore history indefinitely. It is a good idea to clean up these backup and restore history periodically. There are a couple of ways of doing this.

Stored Procedures

There are a couple of stored procedures in msdb database that can be use to clean the tables in msdb database related to backup and restore.

sp_delete_backuphistory

This stored procedure accepts one parameter, which is the oldest_date (date time data type). This stored procedure would delete backup and restore history that are older than the date time value that you’ve specified on the oldest_date parameter.

EXEC msdb.dbo.sp_delete_backuphistory [@oldest_date =] ‘oldest_date’

For example, let say that you want to delete backup and restore history that are older than April 1, 2010, you can use the following query:

EXEC msdb.dbo.sp_delete_backuphistory ‘4/1/2010’

sp_delete_database_backuphistory

This stored procedure can be use to delete backup and restore history for a given database. It accepts one parameter, which is the database_name.

EXEC msdb.dbo.sp_delete_database_backuphistory [@database_name=] ‘database_name’

For example, let say that you want to delete backup and restore history related to AdventureWorks database, you can use the following query:

EXEC msdb.dbo.sp_delete_database_backuphistory ‘AdventureWorks’

Both stored procedures would delete entries from the following tables:

• backupfile
• backupfilegroup
• restorefile
• restorefilegroup
• restorehistory
• backupset
• backupmediafamily
• backupmediaset
• logmarkhistory (by way of delete trigger on backupset table).

For more information on what these tables are used for, you can go here (http://msdn.microsoft.com/en-us/library/aa260604(SQL.80).aspx).

Maintenance Plans

Alternatively, you can create maintenance plan to delete backup and restore history. All you need to do is to create maintenance plan that include History Cleanup Task. One thing to note is that History Cleanup Task does not only delete the backup and restore history, but also delete SQL job history and maintenance plan log. It executes these three stored procedures:

• sp_delete_backuphistory
• sp_purge_jobhistory
• sp_maintplan_delete_log

Take Away

It is a good idea to clean backup and restore history periodically. You can either create and schedule maintenance plan to run on regular basis or create and schedule SQL job using sp_delete_backuphistory stored procedure.