x

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

want to delete i day back bak,trn file using script.
pls help
more ▼

asked Oct 12, 2010 at 08:39 AM in Default

mandara gravatar image

mandara
39 10 10 10

Hi Sir,

VB Script is not running in sql job.Is thr any way to execute it .

Pls suggest.
Oct 12, 2010 at 12:10 PM mandara
See below.
Oct 12, 2010 at 12:23 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest

Well, you could try the undocumented / unsupported extended stored procedure [xp_delete_file][1]... (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.

[1]: http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx
more ▼

answered Oct 12, 2010 at 10:35 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

I like the VBScript solution.
Oct 12, 2010 at 10:37 AM TimothyAWiseman
Thanks, Tim. It's a helluva lot quicker than the Maintenance Plan / xp_delete_file option.
Oct 12, 2010 at 10:39 AM ThomasRushton ♦
Would have been +2 if I could - 1 for the answer and one for understanding the Q!!
Oct 13, 2010 at 02:57 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Oct 12, 2010 at 10:35 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 21 23 32

There are issues with the delete task for some versions of SQL Server - I think 2005 RTM (and maybe SP1) had a misfeature where the Delete task wouldn't navigate into the first level subdirectories...
Oct 12, 2010 at 10:38 AM ThomasRushton ♦
Thanks for pointing that out, I was not aware of it. Even with that said though, it sounds like the more recent service packs fix the issue. It worked very well for me under SQL Server 2005 SP2 and 2008 before I switched to the python solution.
Oct 12, 2010 at 10:40 AM TimothyAWiseman
Just checked my notes. SQL Server 2005 RTM had the problem.
Oct 12, 2010 at 11:23 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Apr 24, 2011 at 07:43 AM

mazz gravatar image

mazz
0 1

You were doing well right up to "Please forgive me..."

Your site makes the donation option quite clear without soliciting again here.
Apr 24, 2011 at 11:36 AM KenJ
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x193

asked: Oct 12, 2010 at 08:39 AM

Seen: 2435 times

Last Updated: Oct 13, 2010 at 02:37 AM