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