Well, you could try the undocumented / unsupported extended stored procedure xp_delete_file... (assuming you meant you wanted to delete using T-SQL)
Or, if you want to delete using VBScript, it's a relatively straightforward job:
EDIT: Stick that lot into a .vbs file, and then schedule it to run using the Windows Task Scheduler.
I assume you mean you want to delete your backup files after they have reached a certain age. If I am understanding that correctly, then you have a few options.
The first, easiest, and probably best is to include it as part of a SQL Server Maintenance plan. You can easily add it through the GUI if you have a scheduled maintenance plan.
The next way is to do it through T-SQL using
The way I personally use is a python script that runs daily and filters through them (it also does some other things, but those are irrelevant here). Because of its os library and sophistated date handling this is much easier to write in python then in T-SQL. Similarly, Powershell/C#/VB.NET/perl/Etc are all probably better candidates for this then T-SQL. Powershell is probably the best one overall for this task and it is reaching a point where it is good for any serious SQL Server DBA to know powershell. Also remember that after you write a program like this in another language of your choice, you can trigger it with
And another option is to use 3rd party tools that incorporate that as one of their features. For instance Red Gate's SQL Backup will delete the backup files it creates after they reach an age you can configure if you want them to. It also has other very helpful features like encryption and compression and a very user friendly GUI.
answered Oct 12, 2010 at 10:35 AM
I think I have written a WSH VBScript application that does what you want and can be downloaded via the linked to page with an example usages. I use this scripts to delete and/or Archive files to a compressed drive. It is designed to run WSH XML from a BAT file using the windows scheduler. inside each bat file are Delete and/or Archive Jobs that you want to run on servers to keep them clean of files left behind by users or misbehaving application. logs are created for each file that was deleted or moved to the archive location. I created this "archiving" feature because most of the files that are being deleted not mine so I mail out the delete logs for final approval. A job is also run on the Archive location so files are automatically deleted after 90 days. This should give the users enough time to let you know it they would like a file restored
Examples of variables that can be defined via the WSH jobs are ... Name of the files using regular expressions, Age of the file in days, folder to be searched, and log file to create.
Please forgive me as the salesman in me comes out here....:) If it Scripts save you time/effort or eliminates the need to purchase more drive space. Please feel free to donate an amount you feel comfortable with :)
as always suggestion for improvements are always welcome