Here’s a quick script you can run to remove old backups. All you need to do is place it into a Job.
What does this script do?
1. This will first enable the xp_cmdshell command.
2. It pulls a list of every database path & file location
3. Deletes all old backup files ( older than 10 days ) from the locations identified in System tables.
4. This will remove all old files such as: Full Database, Transaction Logs, and Differentials.
All you need to do is place this into a Job, and schedule it to run nightly. Remember the account that the job runs under must have access rights to the backup folders.
use master; set nocount on exec master..sp_configure 'show advanced options', '1'; reconfigure exec master..sp_configure 'xp_cmdshell', '1'; reconfigure declare @delete_old_files varchar(max) declare @today datetime declare @10_days_old datetime declare @20_days_old datetime set @today = (select getdate()) set @10_days_old = @today - 10 -- number of days you want to keep. older files will be deleted. set @20_days_old = @today - 20 -- extra variable if you want to incorporate another delete statement. set @delete_old_files = '' select @delete_old_files = @delete_old_files + 'exec master..xp_cmdshell ''del "' + bmf.physical_device_name + '"'';' + char(10) from msdb..backupset bs join msdb..backupmediafamily bmf on bs.media_set_id = bmf.media_set_id where bs.type in ('D', 'I', 'L' ) and bs.backup_finish_date between @20_days_old and @10_days_old order by bs.backup_finish_date desc exec (@delete_old_files)-- for xml path(''), type