Although there are many ways to solve this problem, we can use T-SQL code to handle this file deletion process. I use the xp_cmdshell and FORFILES commands to process a process similar to the one you listed. You can obtain reliable FORFILES information from Microsoft TechNet, but in this article, I will involve a large number of FORFILES structures and usage methods to achieve your goal.
The FORFILES command selects a subset of the file and executes a command for this subset. This command requires the following parameters and accepts the following variables.
Parameters
Parameter Name |
Description |
/P |
Path |
/M |
Search blocking (*. * by default *.*) |
/S |
If this parameter is included, the subdirectory will be retrieved recursively. |
/C <command> |
For each file in the result set, the command must be included in double quotation marks. The default value is "cmd c/echo @ file" |
/D |
For the date range selected for the file, the latest change date is treated as the file standard. When the/d parameter is in MM/DD/YYYY format, files that meet the specified +/-date standard are also included. When the file format is smallint (-32,768-32,768) file +/-file with a change date +, the number of times from the current date to this date will be included in this file result set. |
Variable
Variable name |
Description |
@ FILE |
File Name |
@ FNAME |
No extension file name |
@ EXT |
File Extension |
@ PATH |
Document path |
@ RELPATH |
File relative path |
@ ISDIR |
If the file type is a directory, determine whether it is TRUE |
@ FSIZE |
File size (measured in bytes) |
@ FDATE |
The latest change date postmark on the file |
@ FTIME |
The latest change time postmark on the file |
Using these parameters, we can construct the following example to solve the problem of deleting backup script files. You can create a script based on the change time, date, or backup type. You can even construct scripts that can refer to two standards at the same time.
We will take a closer look at these possible scripts. Remember, you will execute this process from within the T-SQL code, so you need to summarize these statements in the format of EXEC xp_cmdshell 'forfiles command' in an xp_cmdshell access. Note that in all examples, the/Q and/F labels are used as the DELETE command. This means that this command will use quiet mode (/Q) or even delete files that can only be read (/F ).
Example
When the file change date is later than 10/18/2008, delete all C: Backup directories and. SQL files in its subdirectories.
EXEC xp_cmdshell 'forfiles/p c: BACKUP/s/m *. SQL/d 10/18/2008/c "CMD/C del/Q/F @ FILE "'
When the file is changed more than 30 days ago, delete all C: Backup directories and. SQL files in its subdirectories.
EXEC xp_cmdshell 'forfiles/p c: BACKUP/s/m *. SQL/d-30/c "CMD/C del/Q/F @ FILE "'
When the file is changed more than 30 days ago and the file name starts with "F _", delete all C: Backup directories and. SQL files in its subdirectories.
EXEC xp_cmdshell 'forfiles/p c: BACKUP/s/m F _ *. SQL/d-30/c "CMD/C del/Q/F @ FILE "'