You are here:Home » tsql » OLD DATABASAE BACKUP REMOVE SCRIPT

OLD DATABASAE BACKUP REMOVE SCRIPT

DECLARE @sql varchar(8000)
DECLARE @BAK_PATH VARCHAR(4000)
declare c_bak cursor FAST_FORWARD FOR
select bmf.physical_device_name
from msdb..backupset bs
inner join msdb..backupmediafamily bmf
on (bs.media_set_id=bmf.media_set_id)
where DATEDIFF(DAY,bs.backup_start_date,GETDATE())>30
AND DATEDIFF(DAY,bs.backup_start_date,GETDATE())<=60
OPEN c_bak
FETCH NEXT FROM c_bak INTO @BAK_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'EXEC XP_CMDSHELL '+ ''' DEL '+'"'+@BAK_PATH +'"'+''''+', no_output '
PRINT (@SQL)
EXEC(@SQL)
FETCH NEXT FROM c_bak INTO @BAK_PATH
END
CLOSE C_BAK
DEALLOCATE C_BAK