want to delete i day back bak,trn file using script.

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.

link:http://www.code-bytes.com/Automate_WSH_VBScript_To_Delete_Or_Archive_Files.html

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

John

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 xp_cmdshell. If you want to do this, you need to have xp_cmdshell enabled for the server, you can do this through the sp_configure command. Once that is configured, you can write a script that will pull the directory information into a temp table, sort through it to find the files you want, and then delete them with xp_cmdshell commands. this probably is the hardest option though. T-SQL is notoriously weak on its string parsing and os-integration when compared with other languages.

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 xp_cmdhsell from with a T-SQL script if you want.

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.

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:

Option Explicit
'Delete all SQL Server backup files more than 1 days old
Dim oFS, oSQLBackupFol, oFol, oFil
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oSQLBackupFol = oFS.GetFolder("D:\SQL Server Backups") ' Change this to the right path
For Each oFol IN oSQLBackupFol.SubFolders
      For Each oFil in oFol.Files
           If oFil.DateCreated < Now-1 Then
                 'If you want to make sure it's a .bak or .trn file, check oFil.Name, otherwise
                 'This will delete anything more than a day old in the directory
                 'all first level subdirectories.  (Recursion is left as an exercise for the reader)
                  oFil.Delete                
            End If
      Next
Next

EDIT: Stick that lot into a .vbs file, and then schedule it to run using the Windows Task Scheduler.