delete backup files script works well in sql server 2008 r2 but same one fails in sql server 2012 (forfiles)

Question

Post reply

delete backup files script works well in sql server 2008 r2 but same one fails in sql server 2012 (forfiles)

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
June 4, 2015 at 9:26 am

#301239

HI Guys
I noticed that while the cleanup bkp job runs, it works well in sql 2008 or lower environments but it fails withe errror in sql 2012. Anything I am missing here?
SET @cmd = 'forfiles /P "' + @backup_path + '" /s /m *.bak /D -' + @days + ' /C "cmd /c del @path"'
O/P:-
forfiles /P "D:\BACKUP\" /S /M *.bak /D -3 /C "cmd /c del @PATH"
----Error Msg---------------------
ERROR: Invalid argument/option - '@path'.
Type "FORFILES /?" for usage.
NULL
Thanks.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 1

Tried using this, same error:-

exec master.dbo.xp_cmdshell 'forfiles /P "D:\BACKUP\" /s /m *.bak /d -3 /c "cmd /c del @PATH"'

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

My car is making a funning noise, please fix it.

That's about what you posted above. How about posting the full and complete error message you are getting.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

I found something v interesting. I am extracting the backup_path value using XP_INSTANCE_REGREAD.

If the value return with "\" at the end of the string then only the forfile command fails.

Now I modified my script and added extra checks as in, if the string has "\" at the end, it will pass the value to the delete command w/o using "\" at the end.

It's working good now. I have tested that in few servers, working smoothly.....

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

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

Does you car still make noise now? Lol...:-P:-D;-):-)

Thanks.

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 5

Just out of curiousity how would a file name ever have a \ at the end of it, wouldn't that make it a directory?

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 6

Thnx for asking the question.

I have written to get the backup location first, then running delete command to only delete files from the location(backup Path) which has .bak as extension. Before deleting files I am running a check to count the no of files and after the delete I am running a check to count the remaining files.

Thanks.

Perry Whittle SSC Guru Points: 234040 More actions · Answer 7

just use Powershell, it's much easier

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

"Ya can't make an omelette without breaking just a few eggs"

BLOB EATER SSChampion Points: 14609 More actions · Answer 8

Or look into VB which we use in our little shop - IO.File.Delete(FileName).

Perry Whittle SSC Guru Points: 234040 More actions · Answer 9

BL0B_EATER (6/5/2015)
Or look into VB which we use in our little shop - IO.File.Delete(FileName).

Ooooh that's so yester year, here in the 21st century we use this

$checkdate = (get-date).AddDays(-30)

$checkpath = "C:\Program Files\ Microsoft SQL Server\MSSQL.INST1\MSSQL\Backup\*"

get-childitem $checkpath -Include *.bak | ?{$_.LastWriteTime -lt $checkdate} | remove-item

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

"Ya can't make an omelette without breaking just a few eggs"

delete backup files script works well in sql server 2008 r2 but same one fails in sql server 2012 (forfiles)

Cookies on SQLServerCentral