DELETE OLD BACKUP

Delete old backup

Change the number of days,

specify the number of days, and read the comments

Declare all the variables to be used

------------------------------------

Declare @FileName varchar(100),

@DynDelete varchar(100),

@path varchar(100),

@DifDays varchar (100)=1  --specify number of days--

Enable the xp_cmdshell

----------------------

exec sp_configure 'show advanced options', 1

reconfigure

exec sp_configure 'xp_cmdshell', 1

reconfigure

Create temp tables

------------------

create table #dir

(dir varchar(255))

create table #tobdelted

(nameoffile varchar(255),filedate datetime)

create table #tobdelted2

(nameoffile varchar(255),filedate datetime)

Insert files found into temp table (Please change path)

-------------------------------------------------------

insert into #dir exec master..xp_cmdshell 'dir "C:\Program Files\Microsoft SQL Server\MSSQL11.MATDATACENTRE\MSSQL\Backup"'

set @path= '"C:\Program Files\Microsoft SQL Server\MSSQL11.MATDATACENTRE\MSSQL\Backup\"'

Change the folder path, do not forget the "\" at the end of the path

Insert files to be deleted into appropriate table

-------------------------------------------------

insert into #tobdelted

            (filedate,nameoffile)

select convert(date,(select SUBSTRING(dir,1,10)),110),( SELECT SUBSTRING(

(SELECT SUBSTRING(

(SELECT SUBSTRING(

(SELECT SUBSTRING(DIR, CHARINDEX(' ', DIR) + 1, 255)),

CHARINDEX(' ', DIR),255)),

CHARINDEX(' ', DIR),255)),

(CHARINDEX(' ', DIR)-2),255))

from #dir

where (dir like '%/%%/%') and (dir like '%.trn' or dir like '%.bak')

Insert files to be deleted into appropriate table

-------------------------------------------------

insert into #tobdelted2

            (filedate,nameoffile)

select filedate,nameoffile

from #tobdelted

WHERE (DATEDIFF(month, (select convert(varchar(100),filedate,110)), GETDATE()) = 0

and DATEDIFF(day,(select convert(varchar(100),filedate,110)),getdate())>@DifDays) or

(

DATEDIFF(month, (select convert(varchar(100),filedate,110)), GETDATE())!=0

)

Use Cursor to delete the files

------------------------------

DECLARE curDelFile CURSOR

READ_ONLY

FOR

SELECT[nameoffile]

FROM #tobdelted2

OPEN curDelFile

FETCH NEXT FROM curDelFile INTO @FileName

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

 SET @DynDelete = 'DEL '+@path + @FileName + ' '

 EXEC master..xp_cmdshell @DynDelete

 -- print 'EXEC master..xp_cmdshell'+@DynDelete

END

FETCH NEXT FROM curDelFile INTO @FileName

END

CLOSE curDelFile

DEALLOCATE curDelFile

Delete temp tables

------------------

BEGIN TRY

 drop table #dir

drop table #tobdelted

drop table #tobdelted2

END TRY

BEGIN CATCH

 Print 'Tables do not exist'

END CATCH

DiSable xp_cmdshell proc

------------------------

exec sp_configure 'xp_cmdshell', 0

reconfigure

exec sp_configure 'show advanced options', 0

reconfigure