DELETING BACKUP FILES USING A SCRIPT.

Question

DELETING BACKUP FILES USING A SCRIPT.

Viewing 3 posts - 31 through 32 (of 32 total)

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

steve block SSCertifiable Points: 7698 More actions · Answer 1

andrew,

Maintenance Plans are fine but they will not do what these scripts do. These scripts allow you to keep a specific number sets of backups (a set being a full backup and all subsequent differential and log backups). The Maintenance Plans allow you to delete old backups based upon a time frame (days/weeks), at least that was true in SS 2000 and I haven't found anything different yet in 2008.

steve

Randy Doub SSCertifiable Points: 5322 More actions · Answer 2

I used to backup log files using append with the Date built into the filename. That way I had one file per day and could programtically figure out the filename from x days ago and easily delete using xp_cmdshell. Worked fine. Now I include the Date & Time in my backup file names and use the vbs script below to maintain my backup files. I don't have to know what the file names are. I have this as one of the steps in my backup job. This script will delete all files ending with .bak over 30 days old from whatever folder you want.

Option Explicit

Dim fldrspec, fso, f, fc, fldr, sf, sfc, sfldr

Set fso = CreateObject("Scripting.FileSystemObject")

Set fldr = fso.GetFolder("\\MyServer\MyFolder")

Call FileList(fldr)

Set fso = Nothing

Set fldr = Nothing

Sub FileList(fldr)

Dim dfso, df, df1, dfc, delfile, theDate, x

Set dfso = CreateObject("Scripting.FileSystemObject")

Set df = dfso.GetFolder(fldr)

Set dfc = df.Files

For Each df1 in dfc

theDate = df1.DateLastModified

If RegExpTest(df1.name) = True and DiffDate(theDate) = True Then

Set delfile = dfso.GetFile(df1)

delfile.Delete

End If

Next

Set dfso = Nothing

Set df = Nothing

Set df1 = Nothing

Set dfc = Nothing

Set delfile = Nothing

Set theDate = Nothing

Set x = Nothing

End Sub

'

' We only want to delete files that end with ".BAK"

Function RegExpTest(strng)

Dim regEx, retVal

Set regEx = New RegExp

regEx.Pattern = ".BAK"

regEx.IgnoreCase = true

retVal = regEx.Test(strng)

If retVal Then

RegExpTest = true

Else

RegExpTest = false

End If

Set regEx = Nothing

Set retVal = Nothing

End Function

'

' File must be older than 30 days to qualify

Function DiffDate(theDate)

If DateDiff("d", theDate, Now) > 30 Then

DiffDate = True

Else

DiffDate = False

End If

End Function

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 3

THE-FHA (4/14/2010)
EXECUTE master.dbo.xp_delete_file 0,N'D:\BackupFolder',N'BAK',@DelDate, 1
LET'S SAY I WANT TO DELETE TODAYS BACKUP just to test if you script works.
send me your code.
One more thing do i put the backup and with its extention 'xxxx.bk` or just the backupname?

So I'm guess the original poster has abandoned his post since last reply is from 04/14?

Anyway the xp_delete_file is an undocumented procedure that came around in SQL 2000 (I think). It will only work in deleting BAK and TRN files. So if your backups are done with a BK file extension (reminds me of Burger King:hehe:) this procedure will not work for you. You should consider using some of the other scripts mentioned in this post.

As well the fact that this is an undocumented procedure the caveat to using it: it may not exist in future releases of SQL Server (although it still exist in SQL 2008:w00t:. I just take it as you may not get full support from Microsoft if you break something by using it).

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton