How to perform a SQL backup and restore history cleanup

SQL Server stores a complete history of all SQL backup and restore operations, and other historical activities such as activities like Database Mail, Jobs, Log Shipping, Policies, Maintenance Plans, etc. on a server instance in the msdb database.

All of this data SQL Server inserts into the msdb database backup and restore history tables. Each of these tables contains a row of data for a different action:

backupfile – a row for every data or log file that is backed up

backupfilegroup – a row for every filegroup in a backup set

backupmediafamily – a row for every media family. In case that a media family is located in a mirrored media set, the media family has a separate row for each mirrored set in a history table.

backupmediaset – a row for every backup media set

backupset – a row for every backup set

restorefile – a row for every restored file including files restored indirectly by a filegroup name

restorefilegroup – a row for every restored filegroup

restorehistory – a row for every restore operation

Having a large number of databases and performing frequent SQL backup and restore operation results with a large msdb database and it is preferable to perform SQL backup and restore history cleanup of the msdb database history tables.

For example, having a SQL Server instance that is restoring the transaction log backups for a large number of databases every 15 minutes every day, every time that event occurs it performs one insert into an msdb system table. As a result history data in a year get up to a few million rows.

This results with a very large msdb database and this data will remain in the msdb database and it has to be manually removed.

In this article, we will show a couple of solutions to purge the msdb database using native solutions.

Removing the SQL backup and restore history from the msdb database from SQL Server Management Studio

One of the options to delete backup and restore history is to use the Delete backup and restore history information for the databases checkbox.

To use this option:

1. Right click a database in the Object Explorer pane and select the Delete option:

   

2. In the Delete Object dialog check the Delete backup and restore history information for databases option:

   

   Note that using this option will not only delete backup and restore history, it will also drop the whole database.

Removing the SQL backup and restore history from the msdb database using system stored procedures

To delete backup and restore history for a specific database use the following script:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'AdventureWorks2014'
GO

The sp_delete_database_backuphistory stored procedure will delete information from the backup and restore history tables for a specified database. It has to be run from the msdb database.

This method requires a lot of manual work in cases where a backup and restore history has to be deleted for a large number of databases.

To check if there is any orphaned SQL backup and restore history rows left use the following query:

SELECT database_name AS [Database], 
COUNT(backup_set_id) AS Orphans
FROM   backupset
WHERE  database_name NOT IN (SELECT name FROM master.dbo.sysdatabases)
GROUP BY database_name

If executing this query this query returns any results it means that there are databases that have SQL backup and restore history without a physical reference on a backup history table.

To delete the backup history older than a specified date from the msdb database backup and restore history tables use the following script:

USE msdb;
GO
EXEC sp_delete_backuphistory @oldest_date = '01/01/2013';

Performing a SQL backup and restore cleanup operation using this method can be very slow for a large msdb database. Running this command on a large set of data without using indexes may take hours or even days to complete. Another issue is that this will block backups as it will lock the msdb system tables.

In terms of scalability system tables will perform poorly if not correctly indexed, however the msdb database backup and restore tracking tables do not have indexes. Creating indexes on the msdb SQL backup and recovery history tables will handle the performance issue. The following script creates indexes on msdb history tables:

USE msdb
GO

-- create indexes on the backupset column

CREATE INDEX  IX_backupset_backup_set_iid ON backupset(backup_set_id)
GO
CREATE INDEX IX_backupset_backup_set_uuiid ON backupset(backup_set_uuid)
GO
CREATE INDEX IX_backupset_media_set_iid ON backupset(media_set_id)
GO
CREATE INDEX IX_backupset_backup_finish_date_i ON backupset(backup_finish_date)
GO
CREATE INDEX IX_backupset_backup_start_date_i ON backupset(backup_start_date)
GO

-- create index on the backupmediaset column 

CREATE INDEX IX_backupmediaset_media_set_iid ON backupmediaset(media_set_id)
GO

-- create index on the backupfile column 

CREATE INDEX IX_backupfile_backup_set_iid ON backupfile(backup_set_id)
GO

-- create index on the backupmediafamily column 

CREATE INDEX IX_backupmediafamily_media_set_iid ON backupmediafamily(media_set_id)
GO

-- create indexes on the restorehistory column

CREATE INDEX IX_restorehistory_restore_history_iid ON restorehistory(restore_history_id)
GO
CREATE INDEX IX_restorehistory_backup_set_iid ON restorehistory(backup_set_id)
GO

-- create index on the restorefile column

CREATE INDEX IX_restorefile_restore_history_iid ON restorefile(restore_history_id)
GO

--create index on the restorefilegroup column

CREATE INDEX IX_restorefilegroup_restore_history_iid ON restorefilegroup(restore_history_id)
GO

Removing the SQL backup and restore history from the msdb database by using the History Cleanup Task

Removing historical backup and restore information from historical tables in the msdb database can be achieved by using the History Cleanup Task dialog. The History Cleanup Task dialog task supports deleting SQL backup and restore history, SQL Server Agent Job history, and maintenance plan history.

To delete backup and restore history:

1. Right click the Maintenance Plans under the Management node in the Object Explorer pane and select the New Maintenance Plan option:

   

2. Enter a name of the plan in the New Maintenance Plan dialog:

   

3. Select History Cleanup Task from the Maintenance Plan Tasks toolbar:

   

4. In the plan editor double click the selected plan and check the Backup and restore history option:

   

   There is also an option to execute the plan ad hoc or to use the SQL Server Agent scheduler.

   As previous methods this method also can be very slow for large msdb databases.