Agent Jobs

Delete All Old Backup Files

Here’s a quick script you can run to remove old backups. All you need to do is place it into a Job.

Question:
What does this script do?

Answer:
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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s