Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using the FORFILES Command to Delete SQL Server Backups


By:   |   Read Comments (8)   |   Related Tips: More > Backup

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem
Recently I constructed a new backup process that I want to institute globally across all my SQL Server 2005 instances.  This backup process will not only backup all the databases I point it towards, but at the same time will script out the backup commands to a single file in the format of F_YYYYMMDD.sql if the backup process is a full database backup or D|T_YYMMDD_HHMMSS.sql if the backup process is a differential (D) or transaction log (T) backup.  These script files are then stored in a subfolder under the backup directory on the SQL Server.  The process works great, but I only don't want to keep every .sql file.  I cant see the need to keep these scripts after 30 days.  Is there a way I can automate a file deletion process from within SQL Server?

Solution
While there are many ways the file deletion process can be handled with T-SQL code.  I use the xp_cmdshell command along with the FORFILES command for a very similar process to what you have outlined.  Solid information of FORFILES is available from Microsoft TechNet, but I will touch on much of the structure and use of FORFILES for your purposes in this tip.

The FORFILES command will select a subset of files and execute a command against the set. The command requires the following parameters and accepts the following variables:

Parameters

Parameter Name Description
/p Path
/m Search Mask (default is *.*)
/s Subdirectories will be searched recursively if this parameter is included
/c <command> Command to be executed against each file in the result set, commands must be enclosed in double-quotes, default is "cmd c/ echo @file"
/d Date range for file selection, using Last Modified Date as the criterion for the file. When the /d parameter is in the form of MM/DD/YYYY, file meeting the criteria of +/- the specified date are included. When in the format of a smallint (-32,768 - 32,768) the files +/- the files with a modified date +/- that number of days from the current date are included in the file result set.

Variables

Variable Name Description
@FILE File name
@FNAME File name without extension
@EXT File extension
@PATH Full path of the file
@RELPATH Relative path of the file
@ISDIR Evaluates as TRUE if the file type is a directory
@FSIZE File size in bytes
@FDATE Last modified date stamp on the file
@FTIME Last modified timestamp on the file

Using these parameters the following examples could be constructed to take care of your dilemma for deleting your backup script files.  You can create scripts based upon modification date/time or backup type.  You can even construct scripts that utilize both criteria.  We will now take a closer look at these potential scripts.  Remember that you will be executing these from within T-SQL code, so you will need to wrap the statements within an xp_cmdshell call in the format of EXEC xp_cmdshell 'FORFILES COMMAND'.  Please note that in all examples I am using the /Q and /F flags for the del command.  These signify that the command will use quiet mode (/Q) and will even delete read-only files (/F).

Examples

Delete all .sql files in the C:\Backup directory and its subfolders where the file modified date is older 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"'

Delete all .sql files in the C:\Backup directory and its subfolders where the file modified date is more than 30 days old.

EXEC xp_cmdshell 'FORFILES /p c:\BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'

Delete all .sql files in the C:\Backup directory and its subfolders where the file modified date is more than 30 days old and the file name starts with an "F_".

EXEC xp_cmdshell 'FORFILES /p c:\BACKUP /s /m F_*.sql /d -30 /c "CMD /C del /Q /F @FILE"'

Next Steps

  • Modify the code above to fit your retention policy, file structure and needs.
  • Add the modified code to a subsequent job step in the SQL Agent job that runs your backup process.  Include verbose SQL Agent job logging to verify that the process is working correctly.
  • Keep in mind that the FORFILES command does a lot more than just deleting files.  As an example, it can be used to list files for other processes as well.
  • Review other file deletion automation tips from MSSQLTips.com.
  • Review tips on xp_cmdshell at MSSQLTips.com


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, September 27, 2012 - 12:27:45 PM - Will S Back To Top

Example of how to get around the UNC limitation using PUSHD DOS command:

http://www.sqlservercentral.com/Forums/Topic1237283-391-2.aspx

 


Tuesday, June 26, 2012 - 6:24:21 PM - Ken Back To Top

Hi Tim and All

For some reason, the above code never worked for me...I kept playing with it and found a tip at another site. The following line of code now works! Make sure that the "" are implemented before and end of the file location path as well as on the file type.

 

EXEC xp_cmdshell 'FORFILES /p "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup" /s /m "*.bak" /d -3 /c "CMD /C del /Q /F @FILE"'


Saturday, June 23, 2012 - 3:45:30 PM - Ken Back To Top

Hi Tim,

 

I have tried your code and it does not seem to be working...I have sql backup files that are older than 2 days and I am running this command in SQL Server Agent as a Job Step...Below is my code...Any suggestions? Many thanks!

The SQL Agent says the job executed with no errors but the files still remain in the folder....

 

EXEC xp_cmdshell 'FORFILES /p D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup /s /m *.bak /d-2 /c "CMD /C del /Q /F @FILE"'


Tuesday, May 29, 2012 - 4:02:06 PM - Greg Robidoux Back To Top

Got it. Makes sense on the UNC issue.


Tuesday, May 29, 2012 - 3:38:11 PM - Michael B Back To Top

Yes, the thing is forfiles will not work on UNC...

This way you dont have to program in VB/CLR or whatever AND use UNC


Tuesday, May 29, 2012 - 2:11:40 PM - Greg Robidoux Back To Top

@mike B - the link you provided actually uses xp_cmshell as well.  Instead of using "FOREFILES" it is using "DEL" to delete the files.

Here are a couple of other options:

http://www.mssqltips.com/sqlservertip/1374/clr-function-to-delete-older-backup-and-log-files-in-sql-server/

http://www.mssqltips.com/sqlservertip/1486/automate-sql-server-express-backups-and-deletion-of-older-backup-files/

 


Tuesday, May 29, 2012 - 1:19:57 PM - mike B Back To Top

this actually works using all SQL

 

http://www.sqlservercentral.com/scripts/Administration/62729/

 

Just sayin...


Tuesday, November 11, 2008 - 8:09:09 AM - jerryhung Back To Top

Sadly FORFILES does not work with UNC (Network Path)

 and I had to resort to VBScript for a job to cleanup backup files

 

But for local cleanup, FORFILES is much cleaner and better to use


Learn more about SQL Server tools