Sql Delete older than 14 days
I am trying to delete backup files older than 14 days
It is telling me that it did run but the files are in the folder older than 14 days 
It is telling me that it did run but the files are in the folder older than 14 days
Jerryuk007
We encountered the same issue on a MSSQL2005 installation, so we decided to do it by script which worked every time!
ASKER
Do you have the script that you used ?
Try something like this:
Set NoCount On
Declare @bckDate DateTime, @DaysToKeep smallint, @DatabaseName varchar(100),@Path varchar(400)
Declare @SQLCmd varchar(8000), @idx smallint
Set @DaysToKeep=14
Set @Path='F:\Database_Backups\'
Set @DatabaseName='MyDatabaseToBackup'
if(@DatabaseName is null)Set @DatabaseName=(Select db_Name())
Set @idx=0
-- Delete backup which are @DaysToKeep Old and up to 30 days older
While(@idx<30)
Begin
Set @SQLCmd='master..xp_cmdshell ''del '+@Path+convert(varchar,DateAdd(dd,-(@DaysToKeep+@idx),convert(varchar(8),GetDate(),112)),112)+'_'+@DatabaseName+'.bak'',No_OUTPUT'
Exec(@SQLCmd)
Set @idx=@idx+1
End
Print 'Older Backups have been deleted, now taking a new backup!'
Set @SQLCmd='BACKUP DATABASE '+@DatabaseName+' TO DISK='''+@Path+convert(varchar(8),GetDate(),112)+'_'+@DatabaseName+'.bak'' WITH INIT'
Exec(@SQLCmd)
If(@@ERROR=0)
Print 'Backup Completed Successfully!'
Else Print'Backup completed with error, please investigate.'
hey remove dot bak(.bak) and please be sure that your agent services are executing with the same name ,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.