Sql Delete older than 14 days

Aaron Thorn
Aaron Thorn used Ask the Experts™
on
I am trying to delete backup files older than 14 days

 Pictures 1 Pictures 2
It is telling me that it did run but the files are in the folder older than 14 days  Pictures 1  Pictures 3
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
We encountered the same issue on a MSSQL2005 installation, so we decided to do it by script which worked every time!

Author

Commented:
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.'

Open in new window

hey remove dot bak(.bak) and please be sure that your agent services are executing with the same name ,
just keep bak .
agent services just check for the extension ..bak , where it is as .bak bak

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial