Sql Server: Delete/Remove/Clean Up Backup History Script
If you have suddenly discovered that your msdb database is gigabytes large due to the storage of backup/restore history, you may be tempted to use
sp_delete_backuphistoryto trim down the history. Do not be tempted!
sp_delete_backuphistoryis poorly written and will often take days to finish if you have not or seldom maintain your backup history. After seeing logs indicating that
sp_delete_backuphistoryhad run for 100+ hours before ultimately failing, I put together a backup history maintenance script that cleaned up the million plus rows in each of the backup history tables in just over an hour. Also, feel free to try the other alternative
sp_delete_backuphistoryscript that I found.
declare @DaysToRetain INT set @DaysToRetain = 7 SET NOCOUNT ON --delete restorefile delete restorefile from restorehistory rh join restorefile rf on rh.restore_history_id = rh.restore_history_id where rh.restore_date < GetDate()- @DaysToRetain --delete restorefilegroup delete restorefilegroup from restorehistory rh join restorefilegroup rfg on rfg.restore_history_id = rh.restore_history_id where rh.restore_date < GetDate()- @DaysToRetain --delete restorehistory delete restorehistory where restore_date < GetDate()- @DaysToRetain --delete backupfile delete backupfile from backupset bs join backupfile bf on bf.backup_set_id = bs.backup_set_id where bs.backup_finish_date < GetDate()- @DaysToRetain --delete backupset delete backupset where backup_finish_date < GetDate()- @DaysToRetain --delete backupmediafamily delete backupmediafamily from backupmediaset bms left join backupset bs on bms.media_set_id = bs.media_set_id join backupmediafamily bmf on bmf.media_set_id = bms.media_set_id where bs.backup_set_id is null --delete backupmediaset delete backupmediaset from backupmediaset bms left join backupset bs on bms.media_set_id = bs.media_set_id where bs.backup_set_id is null SET NOCOUNT OFF