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 command and the Forfiles command to handle the process similar to what you have listed. Reliable information about forfiles can be obtained from Microsoft TechNet, but in this article, I will involve forfiles a large number of structures and usage methods to achieve your goal.
The forfiles command selects a subset of the file and executes a command against that subset. This command requires the following parameters and accepts the following variables.
Parameters
Name of parameter |
Describe |
/P |
Path |
/m |
Retrieve Masks (*.* by default) |
/s |
If this parameter is included, the subdirectory will be recursively retrieved. |
/C <command> |
To execute a command for each file in the result set, the command must be enclosed in double quotes, and the default is "cmd C/echo @file" |
/d |
For the date range of the file selection, consider the latest change date as a file standard. When the/d parameter is in MM/DD/YYYY format, files that meet the specified +//date criteria are included. The number of times from the current date to that date will be included in the file result set when the file format is a smallint ( -32,768-32,768) file-/-file with a change date/+. |
Variable
Variable name |
Describe |
@FILE |
Filename |
@FNAME |
File name with no extension |
@EXT |
File name extension |
@PATH |
File Absolute Channel |
@RELPATH |
Relative path of File |
@ISDIR |
If the file type is a directory, then determine if it is true |
@FSIZE |
File size (measured in bytes) |
@FDATE |
Latest change Date postmark on file |
@FTIME |
Latest change Time postmark on file |
Using these parameters, you can construct the following example to solve the problem of deleting a backup script file. You can create a script based on the change time/date or backup type. You can even construct scripts that can refer to both standards at the same time.
We'll take a closer look at these possible scripts. Remember that you will perform this process from within the T-SQL code, so you need to summarize the statements in the format of the exec xp_cmdshell ' forfiles COMMAND ' in a xp_cmdshell access. Please note that in all cases I use the/Q and/F tags as the delete command. This means that the command will use Quiet mode (/q) and even delete the only read-only file (/f).
Example
Deletes all c:backup directories and. sql files under its subdirectories when the file change date is later than 10/18/2008.
EXEC xp_cmdshell ' forfiles/p c:backup/s/M *.sql/d 10/18/2008/c ' cmd/c del/q/F @FILE '
Deletes all c:backup directories and the. sql files under its subdirectories when the file change date is more than 30 days.
EXEC xp_cmdshell ' forfiles/p c:backup/s/M *.sql/d -30/c ' cmd/c del/q/F @FILE '
Deletes all c:backup directories and the. sql files under its subdirectories when the file change date is more than 30 days and the filename begins with "f_."
EXEC xp_cmdshell ' forfiles/p c:backup/s/M f_*.sql/d -30/c ' cmd/c del/q/F @FILE '