Gordon-265412 (6/5/2008)
Forfiles is not included in every os by default. I needed to make sure that the code was supportable with no additional external software requirements (resource kit executables and such).The Forfiles command would make life quite a bit simpler and it could be easily added to it and the FOR command removed...
To be fair xp_cmdshell is not enabled on SQL Server instances by default, and is barred from many environments for a long list of good reasons surrounding misuse and security.
PowerShell ships with SQL Server. It is disabled by default, mostly to force you choose how you want to allow it to run in your environment, from wide-open to unrestricted. Open a PowerShell prompt and run this command to set the level to a happy medium so you can get started:
Set-ExecutionPolicy RemoteSigned
Reference: http://technet.microsoft.com/en-us/library/dd347628.aspx
Once PowerShell is allowed here is a one-liner to do the same. Adjust the constants to suit...remove the -WhatIf to have it really do the deletion:
ls -Path "\\FooServer\BarShare\" -Filter "FooFile_*" |? {$_.LastWriteTime -lt (get-date).AddDays(-3)} | Remove-Item -WhatIf
To also look in all sub-folders:
ls -Path "E:\Backups\" -Filter "*.bak" -Recurse |? {$_.LastWriteTime -lt (get-date).AddDays(-3)} | Remove-Item -WhatIf
* You can also change .AddDays to .AddHours if needed
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato