Post reply

SQL Script to DELETE FOLDERS?

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

  • June 10, 2011 at 1:17 am

    #1336813

    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

  • June 10, 2011 at 4:38 am

    #1336875

    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!

  • June 10, 2011 at 12:37 pm

    #1337196

    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

  • June 10, 2011 at 12:38 pm

    #1337197

    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

  • June 10, 2011 at 4:39 pm

    #1337321

    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!

  • July 5, 2011 at 3:19 pm

    #1348610

    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!

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

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