Use the FORFILES command to delete the batch processing of SQLServer backup

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 "'