Post reply

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

  • 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.

  • June 4, 2015 at 9:34 am

    #1803060

    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.

  • June 4, 2015 at 10:14 am

    #1803078

    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.

  • June 4, 2015 at 10:23 am

    #1803079

    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.

  • June 4, 2015 at 10:23 am

    #1803081

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

    Thanks.

  • June 4, 2015 at 10:31 am

    #1803084

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

  • June 4, 2015 at 10:39 am

    #1803088

    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.

  • June 4, 2015 at 11:32 am

    #1803099

    just use Powershell, it's much easier

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

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

  • June 5, 2015 at 1:00 am

    #1803233

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

  • June 5, 2015 at 5:40 am

    #1803269

    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" 😉

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

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