SQL Script to DELETE FOLDERS?

Question

SQL Script to DELETE FOLDERS?

dso808

SSCrazy

Points: 2102
More actions
June 9, 2011 at 6:39 pm

#239041

Hi - I need to create a sql script that will delete FOLDERS older than 2 days. Can anyone please point me in the right direction with some sample script? Thanks in advance to all who reply!

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I suspect T-SQL is not the right tool for this task.

Try powershell instead! Here's a sample: http://sqlserverpedia.com/blog/sql-server-bloggers/xp_delete_file-vs-powershell/

-- Gianluca Sartori

Crispin Proctor SSCoach Points: 18161 More actions · Answer 2

You could use a pretty simple command line delete via xp_cmdshell.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Crispin Proctor (6/10/2011)
You could use a pretty simple command line delete via xp_cmdshell.

xp_cmdshell is disabled on all systems by default since SQL 2005 for a reason. Can it be properly secured, yes, but the steps required to use it securely are often times skipped.

I agree with Gianluca, PowerShell is the better option here. There is rarely a compelling reason to do this kind of work in the data layer unless that's how you prefer to work but I resist the temptation to turn my SQL Server into a system-scripting platform.

Here is a one-line PowerShell snippet that will do what you need:

ls C:\DirectoryName |? {$_.PSIsContainer -and ($_.LastWriteTime -lt (get-date).AddDays(-2))} | Remove-Item -Recurse -WhatIf

The -WhatIf switch will let you see what will be deleted if you ran it for real. Remove the switch to have the command do work.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

PS he script is for PowerShell 2.0. It may work in PowerShell 1.0 but no promises.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Crispin Proctor SSCoach Points: 18161 More actions · Answer 5

Must admit, never played with powershell.

/walks off to go find a book on powershell

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

dso808 SSCrazy Points: 2102 More actions · Answer 6

Hi and so sorry about the delay in response. Looks like Powershell is the next new skillset I'll have to acquire. Thanks to everyone for their input!