Use the Forfiles command to remove batch processing for SQL Server backups _mssql

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 '