Archive old database backup files using forfiles.exe and FOR /F by serverku

Before so many posts published I posted to the old database backup files archive using TSQL and using SSIS. Hope you visit that post and you liked them. I am repeating the same thing here, but it will be with two different methods, the first one with FOR /F command which we saw last time to copy database backups to another location too, second one with forfiles.exe. Let us move on the first method.

FOR /F :  As we discussed earlier post, it used same to traverse rows from generated file from SQLCMD command. Here we do not have values with delims (,) and we have only one values to grab and assign in variables.

SQLCMD -Udba -Pdba@1234 -S"PARESH\MSSQLSERVER2012" -dmsdb -Q"set nocount on ; 
SELECT DISTINCT bmf.Physical_device_name FROM  msdb.dbo.backupset (nolock) bs 
INNER JOIN msdb.dbo.backupmediafamily (nolock) bmf on (bs.media_set_id = bmf.media_set_id) 
WHERE bs.backup_finish_date < DATEADD(Day,-7,GETDATE()) " -o "C:\BackupFile.txt"

FOR /F "tokens=1,1 skip=2 delims=," %%G IN (C:\BackupFile.txt) DO del %%G

Please make sure SQLCMD and FOR /F should be in single line individualized. Running above code and captured snapshot as follows,

forfiles.exe : It is used to delete files in giving directories and subdirectories with a specified day or date. Syntax of it is following, Here /P is a directory, /m for search criteria, /S for subdirectories search, /d for days or date and /C command to fire. Please visit this site for more detail.

forfiles [/p <Path>] [/m <SearchMask>] [/s] [/c "<Command>"] [/d [{+|-}][{<Date>|<Days>}]]

Let us run it and capture snap again with the method,

Hope you have some other methods to archive old database backups. Request to share here!