need T-SQL to delete ALL subdirectories within a Directory

  • Looking for T-SQL command to delete all subdirectories (and their files) within a master directory --- in a single invocation.

    Example, I need to delete ALL subdirectories (and their files) under K:\SQLBackups\MySQLServer

    K:\SQLBackups\MySQLServer\myDB1\FULL\myDB1_FULL_backupfile.bak

    K:\SQLBackups\MySQLServer\myDB1\DIFF\myDB1_DIFF_backupfile.bak

    K:\SQLBackups\MySQLServer\myDB2\FULL\myDB2_FULL_backupfile.bak

    K:\SQLBackups\MySQLServer\myDB2\DIFF\myDB2_DIFF_backupfile.bak

    K:\SQLBackups\MySQLServer\myDB3\FULL\myDB3_FULL_backupfile.bak

    K:\SQLBackups\MySQLServer\myDB3\DIFF\myDB3_DIFF_backupfile.bak

    w/ the final result being a single, empty directory called: K:\SQLBackups\MySQLServer

    I have t-sql to delete files w/in a directory but that would require DOZENs of invocations for SQL servers w/ several databases.

    thx in advance

    BT
  • How about a .BAT file invoked from SQL ?

    Or perhaps FORFILES function ? I use it to delete files older than X days. I don't know what the correct syntax would be in your case, and I think it varies somewhat depending on your version of Windows. Searching "forfiles delete subfolders" brings up quite a few hits.

  • Express12 (1/26/2014)


    Looking for T-SQL command to delete all subdirectories (and their files) within a master directory --- in a single invocation.

    Example, I need to delete ALL subdirectories (and their files) under K:\SQLBackups\MySQLServer

    K:\SQLBackups\MySQLServer\myDB1\FULL\myDB1_FULL_backupfile.bak

    K:\SQLBackups\MySQLServer\myDB1\DIFF\myDB1_DIFF_backupfile.bak

    K:\SQLBackups\MySQLServer\myDB2\FULL\myDB2_FULL_backupfile.bak

    K:\SQLBackups\MySQLServer\myDB2\DIFF\myDB2_DIFF_backupfile.bak

    K:\SQLBackups\MySQLServer\myDB3\FULL\myDB3_FULL_backupfile.bak

    K:\SQLBackups\MySQLServer\myDB3\DIFF\myDB3_DIFF_backupfile.bak

    w/ the final result being a single, empty directory called: K:\SQLBackups\MySQLServer

    I have t-sql to delete files w/in a directory but that would require DOZENs of invocations for SQL servers w/ several databases.

    thx in advance

    Because you'll need to use the "quiet mode" switch, I can't think of anything more dangerous but the following link explains how to do this using a single command.

    http://technet.microsoft.com/en-us/library/bb490990.aspx

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • this script did the trick:

    -- This step will DELETE "ALL" previously gen'd FULL/DIFF/LOG backup files on T: making space for the new Backup files

    --

    -- It is needed as the process does not have enough space on T: to house 2 copies of the backups

    --

    DECLARE @cmd nvarchar(4000), @path nvarchar(4000)

    set @path = 'T:\SQLBackups\MySQLServer'

    SET @cmd = 'RD ' + @path + ' /S /Q' -- Delete Files and the Folder IF all files deletions were successful.

    exec master.dbo.xp_cmdshell @cmd

    BT
  • Express12 (1/27/2014)


    this script did the trick:

    -- This step will DELETE "ALL" previously gen'd FULL/DIFF/LOG backup files on T: making space for the new Backup files

    --

    -- It is needed as the process does not have enough space on T: to house 2 copies of the backups

    --

    DECLARE @cmd nvarchar(4000), @path nvarchar(4000)

    set @path = 'T:\SQLBackups\MySQLServer'

    SET @cmd = 'RD ' + @path + ' /S /Q' -- Delete Files and the Folder IF all files deletions were successful.

    exec master.dbo.xp_cmdshell @cmd

    It sounds like you're doing the deletes BEFORE you've completed a successful backup. If that's true, you're absolutely shooting yourself in the mouth. Take the time to write something that will backup one database at a time and only delete the previous backup MDF and LDF's if and only if the current backup was successful.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • In addition to weekly FULL SQL backups (and daily DIFF backups) we do Netbackups at the server level to a data domain drive w/ de-dup --- and save 4 weeks. Therefore, deleting the SQL backups as the 1st step in the FULL BACKUP job is fine as we can always restore .bak files from our Netbackups.

    Also, I only perform this DELETE on 1 sql server which houses a .BAK file > 1 TB.

    I'm using Ola Hallengren's utilities to do our backups, integrity checks, index optimizations, etc. and that backup processes creates a NEW backup then deletes the old backup.

    BT
  • Netbackup or not, I agree with Jeff - failing to wait until your new backups are verified before deleting your current backups is shooting yourself; perhaps the netbackups make it shooting yourself in the gut - not instant death, but long and painful. Compress your data, or get rid of data you don't need, or old backup/temp tables, or get more drive space, but don't delete backup files before you have new ones, even if you do only want to keep 1 copy (also an increased risk factor).

    If you want to delete backup files, use xp_delete_file - it can handle files in subdirectories (and even if it couldn't, that's a simple loop or cursor off of xp_dirtree), and isn't the same security hole xp_cmdshell is http://www.patrickkeisler.com/2012/11/how-to-use-xpdeletefile-to-purge-old.html

    What is the business purpose for deleting the subdirectories - Ola's procedure is just going to create them again next time, and it won't care if they're already there. That's the part I understand least.

  • The only thing that I didn't agree with there is the notion that xp_CmdShell is a security hole. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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