Delete old backup files

Question

Post reply

Delete old backup files

Mani-584606

SSChampion

Points: 11788
More actions
November 13, 2008 at 7:45 pm

#124310

Hi,
Iam following the below procedure to deleteold backups:
Step1: Deletebackupfiles.vbs
iDaysOld = 7
strPath = "F:\BACKUPS\
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)
Set colSubfolders = objFolder.Subfolders
Set colFiles = objFolder.Files
For Each objFile in colFiles
If objFile.DateLastModified < (Date() - iDaysOld) Then
MsgBox "Dir: " & objFolder.Name & vbCrLf & "File: " & objFile.Name
objFile.Delete
End If
Next
For Each objSubfolder in colSubfolders
Set colFiles = objSubfolder.Files
For Each objFile in colFiles
If objFile.DateLastModified < (Date() - iDaysOld) Then
MsgBox "Dir: " & objSubfolder.Name & vbCrLf & "File: " & objFile.Name
objFile.Delete
End If
Next
Next
Step2:Deletebackupfiles.bat
@echo off
c:\DeleteBackupFiles.vbs (Here, pop window coming while deleting each bakfile. we can add something not pop the window. like /Y or\Y , I tried both but doesn't work.could you plz tell me what exactly do I need to add after
@echo off
c:\DeleteBackupFiles.vbs
Step3: schedule the bat file using scheduler.
But I want to do the same from SQLServer Agent. what are the steps to do?
Thank you
Mani

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 1

[font="Verdana"]If you are using SQL or above you can use Maint cleanup task to delete old backups.

Else you can the above command in SQL server too..

*) Create a new job

*) Create a new step

*) In the step select "Operating system" as type

*) Paste the below command in the window

cscript C:\DeleteBackupFiles.vbs

*) Save and schedule the job, thats it[/font]

Regards..Vidhya Sagar
SQL-Articles

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

If you are using sql 2005 you can do it by creating "Maintenance Cleanup Task" for Backup files. specify the location and extention of file and retention period. Also retention period can be set in hrs too 😀

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

In addtion to this; NO NEED TO WRITE ANY VBS script. 😉

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Mani-584606 SSChampion Points: 11788 More actions · Answer 4

the problem here is it is a clustered environment.

the instance has group1 in cluster administrator contains 3 drives(D,E, F)

Now we created a group2,which contains only one drive Z to keep backups.In the maintaince plan,using cleanup task,when I go to browse for the drives its showing up only D,E,F not Z. So Iam not able to use maintenance task to delete old backup files. What can we do here to visible Z drive in the maintenance task?

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 5

In cluster Admin take the SQL Server offline and add the disk drive as a dependency, bring back online.

Tim White

bang725 Mr or Mrs. 500 Points: 577 More actions · Answer 6

use the below script

_________________________________________________

Dim fso,fol,fx

set fso = CreateObject("Scripting.FileSystemObject")

Set fol = fso.GetFolder("F:\BACKUPS\")

For each fx in fol.Files

if datediff("d",fx.DateLastModified,now()) > 7 and right (fx.path,4) =".bak" then

fso.DeleteFile fx.Path

End if

____________________________________________________

goto- job - newstep -- > os command ---> check vbscirpt --> directly paste above code.

Jerry Hung SSChampion Points: 12968 More actions · Answer 7

bang725 (11/17/2008)
use the below script
_________________________________________________
Dim fso,fol,fx
set fso = CreateObject("Scripting.FileSystemObject")
Set fol = fso.GetFolder("F:\BACKUPS\")
For each fx in fol.Files
if datediff("d",fx.DateLastModified,now()) > 7 and right (fx.path,4) =".bak" then
fso.DeleteFile fx.Path
End if
next
set fso=nothing
____________________________________________________
goto- job - newstep -- > os command ---> check vbscirpt --> directly paste above code.

I don't think this one deletes files in subdirectories

For OP, I tried your code and it worked fine on my computer (found the backup files in the root and the sub-folders)

Comment out the MsgBox line if you don't want a pop up

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Satnam Singh Hall of Fame Points: 3542 More actions · Answer 8

Consider an example wherein i have to delete a backup file older than 2 days.I have a database named example whose backup is happening in D drive.The backup file name is example_db_date.bak.Today being 21st Jan 2010, i want to delete those example db backup files present on D drive which are older than 2 days.

I use xp_cmdshell feature.

Note: Before implementing the below code please ensure that xp_cmdshell feature is enabled in SQL Server 2005 Surface Area Configuration.

DECLARE @DeleteOldDiff varchar(500);

SET @DeleteOldDiff = 'xp_cmdshell ''del "D:\example_db_'+

convert(char(8), getdate()-2,112) +'*.BAK'+'"''' ;

exec (@DeleteOldDiff)