FOR FILES command to delete old backup files on remote server

Question

Post reply

FOR FILES command to delete old backup files on remote server

sql-lover

SSCoach

Points: 18530
More actions
February 24, 2015 at 9:43 am

#314959

I have the need to delete old backup files via TSQL job. Found this solution online:
PushD "\\remoteserver\share\DIFF\" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
Share has the required permissions.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

use a powershell job step instead and use the following

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

$checkpath = "C:\Program Files\MSSQL10_50.MSSQLSERVER\MSSQL\Log\*"

echo "The checkdate is "

echo $checkdate

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

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

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

sql-lover SSCoach Points: 18530 More actions · Answer 2

Thanks Perry. Appreciate your quick response.

But I am trying to stay away from PowerShell. Besides, above will fail remotely unless I use Set-Location. The command will be executed on a remote SQL server.

Current solution I wrote is with PowerShell too, but it runs locally on the backup server. The new solution will run as an added step to the TSQL job, in the actual SQL server itself.

sql-lover SSCoach Points: 18530 More actions · Answer 3

Here is a screenshoot of what I am trying to do:

The command works when running outside of the T-SQL job, let's say, via command prompt. And this is being executed from the SQL server, remotely.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Is using the master.dbo.xp_delete_file stored procedure an option for you?

Edit: It's in 2012, right? I'm still on 2008. :crazy:

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

sql-lover SSCoach Points: 18530 More actions · Answer 5

Fixed! Geoff helped me. He does not know, lol, but he did. I use this thread as a reference. I basically had a typo.

This is the correct syntax:

PushD "\\servername\share\" &&("forfiles.exe" /S /M "*DIFF*.sqb" /d -1 /c "cmd /c del @file") & PopD

It works beautifully now.

🙂

sql-lover SSCoach Points: 18530 More actions · Answer 6

Ed Wagner (2/24/2015)
Is using the master.dbo.xp_delete_file stored procedure an option for you?
Edit: It's in 2012, right? I'm still on 2008. :crazy:

Thanks for reply.

But no, it won't work.

Careful. xp_delete_file name is deceiving. It only works for native SQL backup files, I use RedGate. It is a holdover from SQL 2000. It was created like that by design to minimize the risk of hackers deleting your entire drive.