SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER  PROCEDURE dbo.sp_delete_backup
	@backupDir_dbname varchar(255)
AS
DECLARE
	@year1			varchar(4),
	@month1			varchar(2),
	@day1			varchar(2),
	@sqlstr			varchar(2000)
begin
             
        --Get year & month &day fromat of the day before yesterday
	SET @year1 = substring(convert(varchar,datepart(yyyy,getdate()-2)),1,4)
	SET @month1 =substring(convert(varchar,datepart(mm,getdate()-2)),1,2)
	SET @day1=substring(convert(varchar,datepart(dd,getdate()-2)),1,2)
	if len(@month1)<2 set @month1 = '0' + @month1
	if len(@day1)<2 set @day1 = '0' + @day1	
	
	set @sqlstr='del '+@backupDir_dbname+'_db_'+@year1+@month1+@day1+'*.bak'
	
	select @sqlstr
	
	exec master..xp_cmdshell @sqlstr  
	
	set @sqlstr='del '+@backupDir_dbname+'_tlog_'+@year1+@month1+@day1+'*.trn'
	
	select @sqlstr
	
	exec master..xp_cmdshell @sqlstr  

	set @sqlstr='del '+@backupDir_dbname+'_backup_'+@year1+@month1+@day1+'*.trn'
	
	select @sqlstr
	
	exec master..xp_cmdshell @sqlstr  

        --Get year & month &day fromat of the day before yesterday
	SET @year1 = substring(convert(varchar,datepart(yyyy,getdate()-1)),1,4)
	SET @month1 =substring(convert(varchar,datepart(mm,getdate()-1)),1,2)
	SET @day1=substring(convert(varchar,datepart(dd,getdate()-1)),1,2)
	if len(@month1)<2 set @month1 = '0' + @month1
	if len(@day1)<2 set @day1 = '0' + @day1	
	
	set @sqlstr='del '+@backupDir_dbname+'_db_'+@year1+@month1+@day1+'*.bak'
	
	select @sqlstr
	
	exec master..xp_cmdshell @sqlstr  
	
	set @sqlstr='del '+@backupDir_dbname+'_tlog_'+@year1+@month1+@day1+'*.trn'
	
	select @sqlstr
	
	exec master..xp_cmdshell @sqlstr  
	
	set @sqlstr='del '+@backupDir_dbname+'_backup_'+@year1+@month1+@day1+'*.trn'
	
	select @sqlstr
	
	exec master..xp_cmdshell @sqlstr  
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO