Deleting the Old backups and logs by specific date

Sometimes we run into situation where backups and logs will fill the hard drive. This situation will arise when we dont have maintanence clean up plan for the backups.

At times we may find huge number of files in the hard drive..

Deleting those files using the sql code:


declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(dd, -28, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))
--select @DeleteDate
EXECUTE master.dbo.xp_delete_file 0,N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup',N'bak',@DeleteDate,1

We can delete the files hourly basis also by changing the parameter at line 3 of the above code..