Sql-server – Deleting other files during maintenance plan

backupmaintenance-planssql-server-2012

I have an SQL Server 2012 maintenance plan which does the normal stuff (rebuild indexes and update statistics, back up DBs, deletes .bak files older than X days, etc). Everything is working fine with it, except one Maintenance Cleanup Task:

Maintenance Cleanup Task screenshot

This should be deleting all .7z archives (equivalent to .zip or .rar) which get accidentally left in the nightly backups directory (despite our best efforts, we keep ending up with some people leaving these files on the server…), but we still have old .7z files present in subdirectories (e.g. E:\Backups\foo\bar.7z ) after the maintenance plan runs.

There are no errors in the SQL Server Logs.

I suspect this is because the "Delete files of the following types: Backup files" option is restricting the file types which can be deleted. I've also tried changing that to "Maintenance Plan text reports" with the 7z extension, with no change.

Is it possible to have a maintenance plan delete arbitrary (non-SQL Server) files during a Maintenance Cleanup Task? If so, any idea what I'm doing wrong?

Best Answer

As per MS here

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.

By using the Maintenance Cleanup task, a package can remove the backup files or maintenance plan reports on the specified server. The Maintenance Cleanup task includes an option to remove a specific file or remove a group of files in a folder. Optionally you can specify the extension of the files to delete.

I have not deleted any other extension files via maintenance plans and seems from above that a non sql file may not be deleted via Maint'ce plans:

What we use is a VB script and schedule it via SQL agent job:

Select type as :- Type ActiveX scipt when you create the step for below code in SQL agent job:

On Error Resume Next   
    Dim fso, folder, files, sFolder, sFolderTarget     
    Set fso = CreateObject("Scripting.FileSystemObject")   

    'location of the  files 
    sFolder = "B:\FolderA\ABC\"

    Set folder = fso.GetFolder(sFolder)   
    Set files = folder.Files     

    'used for writing to textfile - generate report on files deleted 
    Const ForAppending = 8 

    'you need to create a folder named "scripts" for ease of file management &  
    'a file inside it named "LOG.txt" for delete activity logging 
    Set objFile = fso.OpenTextFile(sFolder & "\Report\LOG.txt", ForAppending) 

    objFile.Write "================================================================" & VBCRLF & VBCRLF 
    objFile.Write "                     FILE REPORT                " & VBCRLF 
    objFile.Write "                     DATE:  " &    FormatDateTime(Now(),1)   & "" & VBCRLF 
    objFile.Write "                     TIME:  " &    FormatDateTime(Now(),3)   & "" & VBCRLF & VBCRLF 
    objFile.Write "================================================================" & VBCRLF  

    'iterate thru each of the files in the respective folder 
    For Each itemFiles In files  
       'retrieve complete path of file for the DeleteFile method and to extract  
            'file extension using the GetExtensionName method 
       a=sFolder & itemFiles.Name 

       'retrieve file extension  
       b = fso.GetExtensionName(a) 
           'check if the file extension is abc(whatever is the extension of you're file
           If uCase(b)="abc" Then 

               'check if the files are older than 1days 
               If DateDiff("d",itemFiles.DateCreated,Now()) >= 1 Then 

                   'Delete any old files to cleanup folder 
                   fso.DeleteFile a  
                   objFile.WriteLine "FILE DELETED: " & a 
               End If 
           End If 
    Next   

    objFile.WriteLine "================================================================" & VBCRLF & VBCRLF

    objFile.Close 

    Set objFile = Nothing 
    Set fso = Nothing 
    Set folder = Nothing 
    Set files = Nothing

Note* Above scripts work such that you need to create folder Report within you're base folder location and within that place a file LOG.txt which captures or logs the details of files deleted by the process. You can use this additional feature which we use to track, else modify the script as per needs!

Related Question