Script to remove old (.bak) files?

Question

Post reply

Script to remove old (.bak) files?

jsheldon

SSCertifiable

Points: 5250
More actions
January 2, 2008 at 11:15 am

#185243

Looking for a script where it can check the file name and delete the files that are created after x amount of days.
I know it will be with the xp_cmdshell and probably dynamic sql...looking for ideas:
So for example: Today is 1/2/08
I have the following files:
sem12282007.bak modified 12/28/07
sem12292007.bak modified 12/29/07
sem12302007.bak modified 12/30/07
i want to remove any file that is 5 days and greater from today's date.

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

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

You could build a maintenance plan with SSIS that just does maintenance cleanup. Probably be easiest.

Amu SSCommitted Points: 1691 More actions · Answer 2

You can use the xp_sqlmaint for your backup with parameter

-DelBkUps 5DAYS

That will take care of your old backup files older than 5days.

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 3

You could also use an SSIS script task -

Option Strict Off

Imports System

Imports System.Data

Imports System.Math

Imports System.IO

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim strFile As String, strFilePath As String

' Change this to a variable with the backup location

strFilePath = Dts.Variables("varBackupLocation").Value

Dim strFileNames = Directory.GetFiles(strFilePath)

Try

For Each strFile In strFileNames

Dim FileInfo As New System.IO.FileInfo(CStr(strFile))

'For Debug

'System.Windows.Forms.MessageBox.Show(FileInfo.CreationTime)

'System.Windows.Forms.MessageBox.Show(FileInfo.CreationTime < DateTime.Now.AddDays(-3))

If (FileInfo.CreationTime <= DateTime.Now.AddDays(-3)) = True Then

File.Delete(strFile)

End If

Next

Catch ex As Exception

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Tommy

Follow @sqlscribe

jsheldon SSCertifiable Points: 5250 More actions · Answer 4

Thanks all will try the extended stored procedure first I forgot about this...

thecosmictrickster@gmail.com SSChampion Points: 10386 More actions · Answer 5

Here's a VB Script that will do it as well. You can run it from Task Scheduler or SQL Agent (whichever you want). Just remove the .txt from the end & alter the file paths. It's pretty generic - doesn't differentiate between file types, just goes through every file checking the last modified date.

Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 6

To address the original question (though xp_cmdshell can be a major security hole)...

-- This code removes the previous day's backup from this folder

--BIG NOTE: This code is dependent on the backup names having the

-- year/month/day/time in them which occurs if you use the DBMP

--or SQL Litespeed to backup your files instead of T-SQL.

Declare @OldFilename varchar(50),

@TodayDate smalldatetime,

@FileDate smalldatetime,

@removefile sysname,

@Month char(2),

@Day char(2),

@Year char(4),

@BAKDate char(10);

Create Table #OldFileCheck (filename varchar(50));

insert into #OldFileCheck Exec Master..xp_cmdshell "dir /b /o G:\SQL_BAK\";

Select * from #OldFileCheck

set @TodayDate = GetDate();

declare FileDel CURSOR for select filename from #OldFileCheck with (nolock) where filename like 'Apacs_db_20%'

Open FileDel

fetch next from FileDel into @OldFileName

while @@fetch_Status = 0

Begin

Set @Year = (Select substring(@Oldfilename, 10, 4) )

Set @Month = (Select substring(@Oldfilename, 14, 2) )

Set @Day = (Select substring(@Oldfilename, 16, 2) )

Set @BAKDate = @Month + '/' + @Day + '/' + @Year

Set @FileDate = (Select Cast(@BAKDate as DateTime))

If @FileDate < DateAdd(mm,-3,@TodayDate)

Begin

set @removefile = 'Del G:\SQL_BAK\' + @OldFileName;

Exec Master..Xp_cmdshell @removefile;

Select @RemoveFile;

Select 'No files available';

END;

fetch next from FileDel into @OldFileName;

END

Close FileDel

Deallocate FileDel

Drop Table #OldFileCheck

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

jsheldon SSCertifiable Points: 5250 More actions · Answer 7

Thanks again...I went with something a bit more simpler, I realize it is using xp_cmdshell

echo on

rem Delete of SQL Backup Files on HQ/SQLBACKUP% older than 5 days from today's date.

rem The script uses the modified date to compare dates.

FORFILES /p V:\HQ-APPS2\Data /m *.* /s /c "CMD /C del @FILE" /d -5

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 8

FORFILES?

I haven't heard of that command. It works in the command shell?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

steve smith-401573 SSChampion Points: 10889 More actions · Answer 9

A reference for those interested in FORFILES command:

http://technet2.microsoft.com/windowsserver/en/library/9660fea1-65c7-48cf-b466-204ba159381e1033.mspx?mfr=true