SQL Server - delete backup and restore information

Deletion of backup and restore information from the backup and restore history tables can be done in 2 ways:

1. Deletes backup and restore information about a specified database.

All the information regarding the specific database will be deleted.

No choice to delete between dates.

USE msdb;

GO

exec sp_delete_database_backuphistory @database_name = 'DATABASENAME';

GO

2. Deletes backup and restore information older than a specified date.

Can't be run for one or more specific databases.

Will affect all databases.

USE msdb;

GO  

EXEC sp_delete_backuphistory @oldest_date = '2015-01-01'; 

GO

Remarks:

1. The SPS must be run from msdb database.

2. The SPS affects the following tables:

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

Check the deletion by execute a select on one of them:

SELECT * FROM msdb.dbo.backupset;

3. The physical backup files are preserved.