January 2, 2008 at 11:15 am
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.
January 2, 2008 at 12:44 pm
You could build a maintenance plan with SSIS that just does maintenance cleanup. Probably be easiest.
January 4, 2008 at 7:52 am
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.
January 4, 2008 at 8:00 am
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 @sqlscribeJanuary 4, 2008 at 9:54 am
Thanks all will try the extended stored procedure first I forgot about this...
January 6, 2008 at 8:01 pm
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.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
January 7, 2008 at 11:03 am
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
January 7, 2008 at 2:10 pm
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
January 8, 2008 at 3:46 am
FORFILES?
I haven't heard of that command. It works in the command shell?
January 8, 2008 at 8:50 am
A reference for those interested in FORFILES command:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy