SQL Query to Delete the OLD Back up files based on No of Days

Here is a small SQL Script developed to delete the OLD Backup files based on the Date.

All you need to do is just check how many days old files should be deleted and provide the same in

Select statement after get date.

SET NOCOUNT ON

GO

sp_configure ‘show advanced options’, 1

GO

RECONFIGURE

GO

sp_configure ‘xp_cmdshell’, 1

GO

PRINT ‘Running reconfigure’

RECONFIGURE

GO

DECLARE @rc INT

DECLARE @cmd VARCHAR(1000)

DECLARE @cursor CURSOR

DECLARE @physicaldevicename VARCHAR(1000)

DECLARE @Err VARCHAR(1000)

SET @cursor = CURSOR FOR SELECT b.physical_device_name AS physicaldevicename FROM msdb..backupset AS a

inner join msdb..backupmediafamily as b on a.media_set_id = b.media_set_id

WHERE a.backup_start_date

< GETDATE()-5 ORDER BY a.backup_start_date,a.database_name

OPEN @cursor

WHILE 1=1

BEGIN

FETCH FROM @cursor INTO @physicaldevicename

IF @@fetch_status <> 0

BEGIN

SET @Err = @@fetch_status

PRINT @Err

BREAK

END

ELSE

BEGIN

SET @cmd = ‘del ‘ + @physicaldevicename

PRINT ‘Starting deleting the backup file ‘ + @cmd

EXEC @rc = master.dbo.xp_cmdshell @cmd

IF @rc <> 0

BEGIN

PRINT ‘Backup File ‘ + @physicaldevicename + ‘ was not deleted’

END

ELSE

BEGIN

PRINT ‘Backup File ‘ + @physicaldevicename + ‘ deleted successfully at timestamp ‘+ CONVERT(VARCHAR,GETDATE(),109)

END

END

END

CLOSE @cursor

DEALLOCATE @cursor

GO

sp_configure ‘xp_cmdshell’, 0

GO

RECONFIGURE

GO

sp_configure ‘show advanced options’, 0

GO

PRINT ‘Running reconfigure’

RECONFIGURE

GO

SET NOCOUNT OFF

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