Every time you backup or restore a database SQL Server keeps history information about the backup or restore operation. The backup and restore information is captured and stored in the msdb database. This backup and restore information if left unchecked can take up more and more space over time. To minimize msdb storage space you need to consider removing the backup and restore history records when they are no longer useful.
Determining how long to keep backup history information is site specific. Meaning how long to keep the backup and restore information is based on the requirements of an individual environment. For most environments the value of backup and restore information diminishes over time. For example the backup information for the current backup chain has more value than the backup information for a backup taken two months ago, or maybe even a week ago.
SQL Server provides a couple of different ways to delete backup and restore history. If you want to remove backup and restore information for all databases based on a date you can use the “sp_delete_backuphistory” system stored procedure. Or you can use the system stored procedure named “sp_delete_database_backuphistory” if you want to remove all backup and restore history for a specific database.
Example of using sp_delete_backuphistory:
This example will delete all backup and restore history that is older than 14 days
USE msdb;
GO
-- Set date to 14 days prior to today
DECLARE @OldestHistoryDate DATETIME = CONVERT(VARCHAR(10),DATEADD(dd, -14, GETDATE()),10);
-- Display purge date
SELECT 'Purging backkup history before ' +
CONVERT(VARCHAR(10),@OldestHistoryDate,101);
-- Purge backup history
EXEC sp_delete_backuphistory @OldestHistoryDate
GOExample of using sp_delete_database_backuphistory:
This example deletes all the backup history for a database named Contoso.
SELECT 'Delete backup history for database Contoso';
EXEC sp_delete_database_backuphistory 'Contoso';